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

[PrashantSangle]

Try below

testing:
Load
org_Date1,
org_Date2,
Date1,
Date2,
if(Date1< today(),'N', if(Date1='Unknown','Unknown','A')) as Status1,
if(Date2< today(),'N', if(Date2='Unknown','Unknown','A')) as Status2;
Load
Date1 as org_Date1,
Date2 as org_Date2,
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' ) as Date1,

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' ) as Date2;
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
];

Regards,

Prashant Sangle

[Kushal_Chawda]

@vikasshana  I think your conditions are working fine. 12/31/2021 (December 2021) is future Date 2 right? How you are expecting it to be 'N'?

12/31/2020

12/31/2021

N

A

[vikasshana]

Sorry it has to be 'A', updated 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	A
12/31/2019	12/31/2020	N	N
2996	3997	Unknown	Unknown

[PrashantSangle]

@vikasshana , Kushal is highlighting for Status2

12/31/2021 is future date and as per your requirement it has to come as 'A', but in your expected output you mention 'A'

Regards

[PrashantSangle]

@vikasshana ,have you tried my solution.

[vikasshana]

Thanks for that @PrashantSangle , it is working fine as expected. Is there any way to modify my below if statement in order to achieve what I'm looking for?

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,

[PrashantSangle]

You have to replace Date1 with alt() expression which I have written in my reply. 

Basically you have different different date format. First you have to make it in one common format.

Alt() will help you for that.

[vikasshana]

Got it but in real time it is very difficult for me change the code, if there is any way to change it in if condition it will be great helpful.

[PrashantSangle]

You have to change it in you if condition only.

if(Date1< today()

Here you have to replace Date1 with alt() logic which I have written above.

If still face any difficulty, then give me some time I will create that expression and share with you