Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator
Creator

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

 

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:

date1date2Status1Status2
29983999NN
29994000NN
21/08/202121/08/2021NN
22/08/202122/08/2021NN
23/08/202123/08/2021NN
24/08/202124/08/2021NN
25/08/202125/08/2021NN
26/08/202126/08/2021NN
27/08/202127/08/2021AA
28/08/202128/08/2021AA

 

Expected Output:

date1date2Status1Status2
29983999UnknownUnknown
29994000UnknownUnknown
21/08/202121/08/2021NN
22/08/202122/08/2021NN
23/08/202123/08/2021NN
24/08/202124/08/2021NN
25/08/202125/08/2021NN
26/08/202126/08/2021NN
27/08/202127/08/2021AA
28/08/202128/08/2021AA

 

Regards,

Vikas

1 Solution

Accepted Solutions
vikasshana
Creator
Creator
Author

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,

View solution in original post

2 Replies
vikasshana
Creator
Creator
Author

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

vikasshana
Creator
Creator
Author

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,