Need help in IF statement and was not working as I expected

[vikasshana]

Hi,

I do have the below Inline load statement.

testing:
Load * Inline [
Date1, Date2
28/08/2021, 28/08/2021
27/08/2021, 27/08/2021
26/08/2021, 26/08/2021
25/08/2021, 25/08/2021
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 'N', today date and Future 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(), 'N', if(isnull(date(date#(Date1))), 'Unknown', 'A')) as Status1,
if(Date2< today(), 'N', if(isnull(date(date#(Date2))), 'Unknown', 'A')) as Status2
Resident testing;

Drop Table testing;

Output:

date1	date2	Status1	Status2
2998	3999	N	N
2999	4000	N	N
21/08/2021	21/08/2021	N	N
22/08/2021	22/08/2021	N	N
23/08/2021	23/08/2021	N	N
24/08/2021	24/08/2021	N	N
25/08/2021	25/08/2021	N	N
26/08/2021	26/08/2021	N	N
27/08/2021	27/08/2021	A	A
28/08/2021	28/08/2021	A	A

Expected Output:

date1	date2	Status1	Status2
2998	3999	Unknown	Unknown
2999	4000	Unknown	Unknown
21/08/2021	21/08/2021	N	N
22/08/2021	22/08/2021	N	N
23/08/2021	23/08/2021	N	N
24/08/2021	24/08/2021	N	N
25/08/2021	25/08/2021	N	N
26/08/2021	26/08/2021	N	N
27/08/2021	27/08/2021	A	A
28/08/2021	28/08/2021	A	A

Regards,

Vikas

[vikasshana]

Hi,

I do have the below Inline load statement.

testing:
Load * Inline [
Date1, Date2
28/08/2021, 28/08/2021
27/08/2021, 27/08/2021
26/08/2021, 26/08/2021
25/08/2021, 25/08/2021
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 'N', today date and Future 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(), 'N', if(isnull(date(date#(Date1))), 'Unknown', 'A')) as Status1,
if(Date2< today(), 'N', if(isnull(date(date#(Date2))), 'Unknown', 'A')) as Status2
Resident testing;

Drop Table testing;

Output:

date1	date2	Status1	Status2
2998	3999	N	N
2999	4000	N	N
21/08/2021	21/08/2021	N	N
22/08/2021	22/08/2021	N	N
23/08/2021	23/08/2021	N	N
24/08/2021	24/08/2021	N	N
25/08/2021	25/08/2021	N	N
26/08/2021	26/08/2021	N	N
27/08/2021	27/08/2021	A	A
28/08/2021	28/08/2021	A	A

Expected Output:

date1	date2	Status1	Status2
2998	3999	Unknown	Unknown
2999	4000	Unknown	Unknown
21/08/2021	21/08/2021	N	N
22/08/2021	22/08/2021	N	N
23/08/2021	23/08/2021	N	N
24/08/2021	24/08/2021	N	N
25/08/2021	25/08/2021	N	N
26/08/2021	26/08/2021	N	N
27/08/2021	27/08/2021	A	A
28/08/2021	28/08/2021	A	A

Regards,

Vikas

[vikasshana]

@stevejoyce can you help me out with this, now the requirements has been changed.

[vikasshana · Accepted solution]

No worries, I got the solution. I've changed the script like below and is working fine now.

if(isnull(date(date#(Date1))), 'Unknown', if(Date1< today(), 'N','A')) as Status1,
if(isnull(date(date#(Date2))), 'Unknown', if(Date2< today(), 'N','A')) as Status2,