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

[djbloiss]

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]

[ogautier62]

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]

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

[ogautier62]

I mean a join to sales30 or sales14

use just one table

[djbloiss]

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]

now you make one load for item14 in table A,

then another load for item30 in table B

it's that ?