If condition is not doing the changes what I'm expecting

[vikasshana]

Hi,

I do have the below Inline load statement.

testing:
Load * Inline [
Date1, Date2
12/31/2024, 12/31/2023
12/31/2031, 12/31/2031
-, -
-, 31.12.2022
31.12.2025, -
12/31/2020, 12/31/2021
12/31/2019, 12/31/2020
2996, 3997
];

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 or null 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(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,

Resident testing;

Drop Table testing;

Output:

date1	date2	Status1	Status2
12/31/2024	12/31/2023	A	A
12/31/2031	12/31/2031	A	A
-	-	Unknown	Unknown
-	31.12.2022	Unknown	Unknown
31.12.2025	-	Unknown	Unknown
12/31/2020	12/31/2021	N	A
12/31/2019	12/31/2020	N	N
2996	3997	Unknown	Unknown

Expected Output:

date1	date2	Status1	Status2
12/31/2024	12/31/2023	A	A
12/31/2031	12/31/2031	A	A
-	-	Unknown	Unknown
-	31.12.2022	Unknown	A
31.12.2025	-	A	Unknown
12/31/2020	12/31/2021	N	N
12/31/2019	12/31/2020	N	N
2996	3997	Unknown	Unknown

Regards,

V Shana

[vikasshana]

Thanks for that @PrashantSangle , I will wait for your expression, in the mean while I will try to implement your suggestion in my main code.

[vikasshana]

@PrashantSangle I tried the below if condition, can you review the same at your and check please. I tried it in my end and it seems to be working.

if(alt(date#(Date1,'YYYY/MM/DD'),
date#(Date1,'DD/MM/YYYY'),
date#(Date1,'MM.DD.YYYY'),
date#(Date1,'MM/DD/YYYY'),
date#(Date1,'DD-MM-YYYY'),
date#(Date1,'DD.MM.YYYY'),
'Unknown')='Unknown','Unknown',if(alt(date#(Date1,'YYYY/MM/DD'),
date#(Date2,'DD/MM/YYYY'),
date#(Date2,'MM.DD.YYYY'),
date#(Date2,'MM/DD/YYYY'),
date#(Date2,'DD-MM-YYYY'),
date#(Date2,'DD.MM.YYYY'))< today(),'N','A')) as Status1,
if(alt(date#(Date2,'YYYY/MM/DD'),
date#(Date2,'DD/MM/YYYY'),
date#(Date2,'MM.DD.YYYY'),
date#(Date2,'MM/DD/YYYY'),
date#(Date2,'DD-MM-YYYY'),
date#(Date2,'DD.MM.YYYY'),
'Unknown')='Unknown','Unknown',if(alt(date#(Date2,'YYYY/MM/DD'),
date#(Date2,'DD/MM/YYYY'),
date#(Date2,'MM.DD.YYYY'),
date#(Date2,'MM/DD/YYYY'),
date#(Date2,'DD-MM-YYYY'),
date#(Date2,'DD.MM.YYYY'))< today(),'N','A')) as Status2

[PrashantSangle · Accepted solution]

Load
Date1,
Date2,
if(alt(date#(Date1,'YYYY/MM/DD'),
date#(Date1,'DD/MM/YYYY'),
date#(Date1,'MM.DD.YYYY'),
date#(Date1,'MM/DD/YYYY'),
date#(Date1,'DD-MM-YYYY'),
date#(Date1,'DD.MM.YYYY'),
'Unknown')='Unknown', 'Unknown',
if(alt(date#(Date1,'YYYY/MM/DD'),
date#(Date1,'DD/MM/YYYY'),
date#(Date1,'MM.DD.YYYY'),
date#(Date1,'MM/DD/YYYY'),
date#(Date1,'DD-MM-YYYY'),
date#(Date1,'DD.MM.YYYY'),
'Unknown')< today(), 'N','A')) as Status1,

if(alt(date#(Date2,'YYYY/MM/DD'),
date#(Date2,'DD/MM/YYYY'),
date#(Date2,'MM.DD.YYYY'),
date#(Date2,'MM/DD/YYYY'),
date#(Date2,'DD-MM-YYYY'),
date#(Date2,'DD.MM.YYYY'),
'Unknown')='Unknown', 'Unknown',
if(alt(date#(Date2,'YYYY/MM/DD'),
date#(Date2,'DD/MM/YYYY'),
date#(Date2,'MM.DD.YYYY'),
date#(Date2,'MM/DD/YYYY'),
date#(Date2,'DD-MM-YYYY'),
date#(Date2,'DD.MM.YYYY'),
'Unknown')< today(), 'N','A')) as Status2
Inline [
Date1, Date2
12/31/2024, 12/31/2023
12/31/2031, 12/31/2031
-, -
-, 31.12.2022
31.12.2025, -
12/31/2020, 12/31/2021
12/31/2019, 12/31/2020
2996, 3997
];

[PrashantSangle]

Yes you have to do in that way

[vikasshana]

Got it, thank you @PrashantSangle