topic Re: Load Data Dependent on Values from Previous Table in App Development

Load Data Dependent on Values from Previous Table

Meghann_MacDonald — Mon, 20 Dec 2021 21:08:46 GMT

Hi all,

I want to only load values from Table B that are labeled 'Lead' in Table A.

Ex:

[Table A]:

LOAD

[Registration Email],

[Status],

[user_id]

From ABC;

[Table B]:

email as [Registration Email],

Alternate data (leadinfo, etc)

From XYZ (where the email in Table A is listed as 'Lead' for [Status];

Is this possible?

Meghann

Re: Load Data Dependent on Values from Previous Table

Anil_Babu_Samineni — Tue, 17 Nov 2020 20:02:44 GMT

Can you point out with sample data how you should need as outcome? We can see the possibility?

Re: Load Data Dependent on Values from Previous Table

TiagoCardoso — Tue, 17 Nov 2020 20:07:46 GMT

HI, sorry for my english.

Try this, maybe can help you.

[Table A]:

LOAD

[Registration Email],

[Status],

[user_id]

From ABC;

LEFT KEEP([Table A])

[Table B]:

email as [Registration Email],

Alternate data (leadinfo, etc)

From XYZ (where the email in Table A is listed as 'Lead' for [Status];

Re: Load Data Dependent on Values from Previous Table

Meghann_MacDonald — Tue, 17 Nov 2020 20:11:42 GMT

Hi Anil, sure.

Table A currently yields something like this:

So, I want Table B to load any matching emails that are considered 'leads', aka I want it to load accompanying data for the emails in row 2 and 4. That table would be from a different source. I don't think this matters but I will be pulling this kind of thing:

Re: Load Data Dependent on Values from Previous Table

Meghann_MacDonald — Tue, 17 Nov 2020 20:14:23 GMT

Hi @TiagoCardoso , I have tried Left Keep but it includes/keeps all the emails that match, not only the ones that are considered 'Lead'. The place I typed "(where the email in Table A is listed as 'Lead' for [Status]" is what I need converted into code! (aka XYZ source has all of the emails and their data but I don't want all of it)

Re: Load Data Dependent on Values from Previous Table

Anil_Babu_Samineni — Tue, 17 Nov 2020 20:15:01 GMT

how is that Table B looks like, Based on that I can print something for you, If that we have workaround.

Re: Load Data Dependent on Values from Previous Table

Meghann_MacDonald — Tue, 17 Nov 2020 20:18:04 GMT

Hi @Anil_Babu_Samineni , I do not know what you mean.

Re: Load Data Dependent on Values from Previous Table

TiagoCardoso — Tue, 17 Nov 2020 20:30:43 GMT

[Table A]:

LOAD

[Registration Email],

[Status],

[user_id]

From ABC;

NOCONCATENATE temp: LOAD [Registration Email] RESIDENT [Table A] WHERE Status = 'Lead';

LEFT KEEP (temp)
[Table B]:

email as [Registration Email],

Alternate data (leadinfo, etc)

From XYZ (where the email in Table A is listed as 'Lead' for [Status];

DROP TABLE temp;

Re: Load Data Dependent on Values from Previous Table

TiagoCardoso — Tue, 17 Nov 2020 20:35:48 GMT

Maybe too

[Table A]:

LOAD

[Registration Email],

[Status],

[user_id]

From ABC;

MapTemp:MAPPING LOAD [Registration Email],1 RESIDENT [Table A] WHERE Status = 'Lead';

[Table B TEMP]:

email as [Registration Email],

APPLYMAP('MapTemp',email,0) AS Field,

Alternate data (leadinfo, etc)

From XYZ (where the email in Table A is listed as 'Lead' for [Status];

NOCONCATENATE [Table B]: LOAD * RESIDENT [Table B TEMP] WHERE Field = 1;

DROP FIELD Field FROM [Table B];

DROP TABLE [Table B TEMP];

Re: Load Data Dependent on Values from Previous Table

Anil_Babu_Samineni — Tue, 17 Nov 2020 20:34:47 GMT

Sorry, I mean your destination table how it is. Anyway, Can you reload like this way and see If that meets what you need. If not, Please explain based on given sample data to get work

[Table A]:
LOAD email as [Registration Email], leadsource, registrationdate Inline [
email, leadsource, registrationdate
abc@gmail.com, web, 1/1/2011
qlik@qlik.com, organic, 1/1/2020
];

[Table B]:
LOAD * Inline [
Registration Email, Status, user_id
abc@gmail.com, Lead, 1
xyz@gmail.com, Contact, 2
qlik@qlik.com, Lead, 3
] Where Exists([Registration Email]) and Status='Lead';

Re: Load Data Dependent on Values from Previous Table

QFabian — Tue, 17 Nov 2020 20:37:19 GMT

HI @Meghann_MacDonald , it is posible, you can use exists function in where of second load :

[Table A]:

LOAD

[Registration Email],

[Status],

[user_id]

From ABC

Where Status = 'Lead'; //do this or create another table with the values needes (Status = 'Lead')

[Table B]:

email as [Registration Email],

Alternate data (leadinfo, etc)

From XYZ

where exists([Registration Email], email);

Re: Load Data Dependent on Values from Previous Table

Meghann_MacDonald — Tue, 17 Nov 2020 20:59:02 GMT

I think this works perfectly. Nifty trick I will use later on. It does create a bit of roundabout loading but it seems like that is the best way to get what I need. Thank you so much!

Re: Load Data Dependent on Values from Previous Table

Meghann_MacDonald — Tue, 17 Nov 2020 21:00:59 GMT

Hi Anil, the destination table is just what I would LIKE to be able to pull. I just used Tiago's process to do this of loading a temp table from A with only the emails I want, and then loading B with a left keep of (temp). This worked.

I don't have experience loading inline like you listed and I have >8,000 emails so I'm not sure that would work...

Thank you for the help!