IF statement is not working as I expected.

[vikasshana]

Hi,

I do have the below Inline load statement.

testing:
Load * Inline [
Date1, Date2
24/08/2021, 24/08/2021
24/08/2021, 24/08/2021
23/08/2021, 23/08/2021
22/08/2021, 22/08/2021
21/08/2021, 21/08/2021
2999, 4000
2998, 3999
];

Now the requirement is if the date1 is before today it has to be 'Y', remaining dates it has to be 'A' and not relevant date it has to be 'Unknown', same applies for date2 as well.

I tried the below code and it is not working.

Testing_Load:
Load *,
if(date1 < today(),'Y',if(date1<> today(),'A','UnKnown')) as Status1,
if(date2 < today(),'Y',if(date2<> today(),'A','UnKnown')) as Status2,
Resident testing;

Drop Table testing;

Output:

date1	date2	Status1	Status2
2998	3999	Y	Y
2999	4000	Y	Y
21/08/2021	21/08/2021	Y	Y
22/08/2021	22/08/2021	Y	Y
23/08/2021	23/08/2021	Y	Y
24/08/2021	24/08/2021	UnKnown	UnKnown

Expected Output:

date1	date2	Status1	Status2
2998	3999	UnKnown	Unknown
2999	4000	UnKnown	Unknown
21/08/2021	21/08/2021	A	A
22/08/2021	22/08/2021	A	A
23/08/2021	23/08/2021	Y	Y
24/08/2021	24/08/2021	A	A

Regards,

Vikas

[Taoufiq_Zarra]

@vikasshana  can you elaborate

2998 are there date or just a number?

why 24/08/2021 is Y 24/08/2021<today() is false ?

[vikasshana]

sorry for the confusion, below is the updated requirement.

If the date1 is yesterday it has to be 'Y', apart from yesterday's date all other dates are 'A' and if the irrelevant dates like 2998 it has to be 'Unknown'.

[stevejoyce · Accepted solution]

Testing:

Load
Date1
,Date2
,if(Date1 = today()-1, 'Y', if(isnull(date(date#(Date1))), 'Unknown', 'A')) as Status1
,if(Date2 = today()-1, 'Y', if(isnull(date(date#(Date2)) ), 'Unknown', 'A')) as Status2
;

Load * Inline [
Date1, Date2
24/08/2021, 24/08/2021
24/08/2021, 24/08/2021
23/08/2021, 23/08/2021
22/08/2021, 22/08/2021
21/08/2021, 21/08/2021
2999, 4000
2998, 3999
];

[vikasshana]

Thanks for the solution Steve, It is working as I expected.