Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
djbloiss
Contributor III
Contributor III

If x value check if exists in Table A, If y check if exists in Table B

I have This code below.  it all works except for the if statement.  In the If statement I need to do the lookup and if it is 14 then I need to look if the ItemKey value exists in TableA and if the lookup value is 30 then look in TableB for the same ItemKey

SalesIn30Days:

Load

    SO_Closed as SalesClosed30,

    DateKey as SalesDate30,

    ItemKey as Item30

Resident Transactions

Where SO_Closed = 1 and DateKey >= Today() - 30;

SalesIn14Days:

Load

    SalesDate30 as Date14,

    Item30 as Item14

Resident SalesIn30Days

Where SalesDate30 >= Today() - 14;

Items:

Load

    *,

    if(Lookup('DaysTillDead', 'Class code', [Class code], 'ItemClass')=14, 1) as [Item Is Dead]

Resident Temp_Items;

DROP Tables Temp_Items, SalesIn30Days, SalesIn14Days;

Something like this:

if((Lookup('DaysTillDead', 'Class code', [Class code], 'ItemClass')=14 AND Exists(ItemKey, TableA)) OR (Lookup('DaysTillDead', 'Class code', [Class code], 'ItemClass')=30 AND Exists(ItemKey, TableB)) as [Item Is Dead]

5 Replies
ogautier62
Specialist II
Specialist II

Hi,

maybe you could use only one table with a flag for 14 days,

more simple than two table  and a conditional join

regards

djbloiss
Contributor III
Contributor III
Author

Oliver that is interesting. I can do the 14 day flag easily.  What do you mean by a conditional join?

ogautier62
Specialist II
Specialist II

I mean a join to sales30 or sales14

use just one table

djbloiss
Contributor III
Contributor III
Author

okay now I have this,

How would you suggest I do the testing if they are less than 30 days or less than 14 days?

SalesIn30Days:

Load

    SO_Closed as SalesClosed30,

    DateKey as SalesDate30,

    ItemKey as Item30,

    if (DateKey >- Today() - 14, 1) as SoldIn14Days

Resident Transactions

Where SO_Closed = 1 and DateKey >= Today() - 30;

Items:

Load

    *,

    if(Lookup('DaysTillDead', 'Class code', [Class code], 'ItemClass')=14, 1) as [Item Is Dead]

Resident Temp_Items;

ogautier62
Specialist II
Specialist II

now you make one load for item14 in table A,

then another load for item30 in table B

it's that ?