Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Add all matched values from tables B and C to Table A

Hi!! I've been having a really rough time trying to solve this but i keep on getting bad Data. The context is i'm trying to join Leads with effective purchases, and i need all the values from the purchases to add on to the matching Social Security numbers on my Table of Leads (or another table alltogether) in order to understand conversion rate values.

The challenge i'm trying to figure out is that in Tables B and C, sometimes the Social security numbers are duplicated because one person can be the buyer for several products, so I can only count the record once or I get crazy conversion rates (Ex: Google shows, for product B, 120% conversion rate with 40 leads, and more than 40 sales because it isnt de-duplicating correctly with "DISTINCT")

What I need to figure out is how to load the following so that the Lead, that has richer data, joins with the sales data from tables B and C.

Here's an example of the data i'm loading:

Table A (LEADS):

SOCIAL SECURITY NUMBER,

Origin Platform (Facebook/Google/ETC),

Name,

Last Name,

If lead is PAID or ORGANIC,

PRODUCT INTERESTED IN (between A and B),

DATE

TABLE B (PURCHASES PRODUCT A):

SOCIAL SECURITY NUMBER (OF PURCHASER),

DATE (OF PURCHASE),

TABLE C (PURCHASES OF PRODUCT B):

SOCIAL SECURITY NUMBER (OF PURCHASER),

DATE (OF PURCHASE).

 

Can anybody help me solve this? It's eating my brain away.

2 Replies
Highlighted
Master
Master

can you share a sample data and the expected output from this data?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Contributor
Contributor

Hi!

I've shared the sample data in the attached image, showing conversion rates over 2000%. The data that messes things up is "sales", where i'm trying to count "(matches between A and B) + (Matches between A and C)".

In general, the conversion rates should fluctuate between 1 and 10% according to each platform.