Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys,
Wondering if you can assist me with the below.
Working with some data that is submitted by customers through a webpage. There are a few different webpages that contain two forms which are independent, but when the user hits submit, both the forms submit at the same time.
Webpage 1: Retail Inquiry Form + Product Interest Form
Webpage 2: Wholesale Inquiry + Product Interest Form
Webpage 3: Trade Account+ Product Interest Form
Each form output a single row with multiple column. Because of the way the CMS works, the Appeal Source and certain other columns are only stored for the first form. I would like to populate these missing columns from the first form. In this instance, I would like to populate the Appeal source for the Form 'Product Interest'.
All the data is exported to a single table in the database. I plan to import this data twice, once matching anything in the column 'FORM ID' = Product Interest and another time != Product Interest.
Then I aim to match several columns together and then if there is a match, output the Appeal source that exists, however I am struggling to get going.
As the form submits both forms at the same time, the date and time will match for both form submission. The customer ID will be unique and is generated based on their email. Because there could be a chance that individuals submit the form at the same time, I would like to match, 'date' + 'time' + 'customer id'. If this match, then output appeal id in a new column.
The Appeal Source is a conditional column at Query Level.
What the data looks like.
Customer ID | Form ID | Date | Time | Appeal | |
0001 | mark@example.com | Retail Inquiry | 24 June 2019 | 13:16:36 | Classifieds |
0001 | mark@example.com | Product Interest | 24 June 2019 | 13:16:36 | |
0002 | john@example.com | Trade Account | 24 June 2019 | 13:24:07 | |
0002 | john@example.com | Product Interest | 24 June 2019 | 13:24:07 | |
0003 | david@example.com | Wholesale Inquiry | 24 June 2019 | 13:33:28 | |
0003 | david@example.com | Product Interest | 24 June 2019 | 13:33:28 | |
0004 | pablo@example.com | Retail Inquiry | 24 June 2019 | 19:08:49 | Trade Show - Domotex |
0004 | pablo@example.com | Product Interest | 24 June 2019 | 19:08:49 | |
0005 | ryan@example.com | Trade Account | 24 June 2019 | 19:08:49 | |
0005 | ryan@example.com | Product Interest | 24 June 2019 | 19:08:49 |
Hi @Anonymous ,
What is your desired output with above samle table?
Regards,
Yuliana Gu
@v-yulgu-msft wrote:Hi @Anonymous ,
What is your desired output with above samle table?
Regards,
Yuliana Gu
Hi,
Once I split the data into tables:
One Table will contain everything that matches Form ID = Product Interest
Another Table will contain everything else that does not match Form ID = Product Interest
Ideally I would like each row to have the Appeal label, by matching the columns: Customer ID; Time; Date
Thanks
Figo
Table 1:
Customer ID | Form ID | Date | Time | Appeal | |
0001 | mark@example.com | Product Interest | 24 June 2019 | 13:16:36 | Classified |
0002 | john@example.com | Product Interest | 24 June 2019 | 13:24:07 | |
0003 | david@example.com | Product Interest | 24 June 2019 | 13:33:28 | |
0004 | pablo@example.com | Product Interest | 24 June 2019 | 19:08:49 | Trade Show - Domotex |
0005 | ryan@example.com | Product Interest | 24 June 2019 | 19:08:49 |
Table 2:
Customer ID | Form ID | Date | Time | Appeal | |
0001 | mark@example.com | Retail Inquiry | 24 June 2019 | 13:16:36 | Classifieds |
0002 | john@example.com | Trade Account | 24 June 2019 | 13:24:07 | |
0003 | david@example.com | Wholesale Inquiry | 24 June 2019 | 13:33:28 | |
0004 | pablo@example.com | Retail Inquiry | 24 June 2019 | 19:08:49 | Trade Show - Domotex |
0005 | ryan@example.com | Trade Account | 24 June 2019 | 19:08:49 |
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |