cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Figo7 Regular Visitor
Regular Visitor

Vlookup /Matching Rows at Query

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.

3 REPLIES 3
Figo7 Regular Visitor
Regular Visitor

Re: Vlookup /Matching Rows at Query

What the data looks like.

 

Customer IDEmailForm IDDateTimeAppeal
0001mark@example.comRetail Inquiry24 June 201913:16:36Classifieds
0001mark@example.comProduct Interest24 June 201913:16:36 
0002john@example.comTrade Account24 June 201913:24:07email
0002john@example.comProduct Interest24 June 201913:24:07 
0003david@example.comWholesale Inquiry24 June 201913:33:28google
0003david@example.comProduct Interest24 June 201913:33:28 
0004pablo@example.comRetail Inquiry24 June 201919:08:49Trade Show - Domotex
0004pablo@example.comProduct Interest24 June 2019

19:08:49

 
0005ryan@example.comTrade Account24 June 2019

19:08:49

 
0005ryan@example.comProduct Interest24 June 2019

19:08:49

 
v-yulgu-msft Super Contributor
Super Contributor

Re: Vlookup /Matching Rows at Query

Hi @Figo7 ,

 

What is your desired output with above samle table?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Figo7 Regular Visitor
Regular Visitor

Re: Vlookup /Matching Rows at Query


@v-yulgu-msft wrote:

Hi @Figo7 ,

 

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 IDEmailForm IDDateTimeAppeal
0001mark@example.comProduct Interest24 June 201913:16:36Classified
0002john@example.comProduct Interest24 June 201913:24:07email
0003david@example.comProduct Interest24 June 201913:33:28google
0004pablo@example.comProduct Interest24 June 2019

19:08:49

Trade Show - Domotex
0005ryan@example.comProduct Interest24 June 2019

19:08:49

 

 

Table 2:

 

Customer IDEmailForm IDDateTimeAppeal
0001mark@example.comRetail Inquiry24 June 201913:16:36Classifieds
0002john@example.comTrade Account24 June 201913:24:07email
0003david@example.comWholesale Inquiry24 June 201913:33:28google
0004pablo@example.comRetail Inquiry24 June 201919:08:49Trade Show - Domotex
0005ryan@example.comTrade Account24 June 2019

19:08:49

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 174 members 1,959 guests
Please welcome our newest community members: