Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Where Exist Clause

Hi All

I have a scrip below...

Load
BIUNIQUEID,
IF([Termination Date] <= Today(), 'Terminated',
IF(InMonth([Termination Date],Today(),0),'This Month',
IF(InMonth([Termination Date],Today(),1),'Next Month',
IF(InMonth([Termination Date],Today(),2),'2 Month',
IF(InMonth([Termination Date],Today(),3),'3 Month', 'More Than 3 Months')))))as Flag
Resident
STAFF;

For the last "If" statement, in the "Else" part (More Than 3 Months), I only want to return records where [Termination Date] exist.

How can I do this?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

This will load only records that have a Termination Date value:


Load
BIUNIQUEID,
IF([Termination Date] <= Today(), 'Terminated',
IF(InMonth([Termination Date],Today(),0),'This Month',
IF(InMonth([Termination Date],Today(),1),'Next Month',
IF(InMonth([Termination Date],Today(),2),'2 Month',
IF(InMonth([Termination Date],Today(),3),'3 Month', 'More Than 3 Months')))))as Flag
Resident
STAFF

WHERE len(trim([Termination Date]));



This will load all records, but leave the Flag field empty if the record has no value in the Termination Date field


Load
BIUNIQUEID,
IF([Termination Date] <= Today(), 'Terminated',
IF(InMonth([Termination Date],Today(),0),'This Month',
IF(InMonth([Termination Date],Today(),1),'Next Month',
IF(InMonth([Termination Date],Today(),2),'2 Month',
IF(InMonth([Termination Date],Today(),3),'3 Month', if(len(trim([Termination Date])),'More Than 3 Months'))))))as Flag
Resident
STAFF;


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

This will load only records that have a Termination Date value:


Load
BIUNIQUEID,
IF([Termination Date] <= Today(), 'Terminated',
IF(InMonth([Termination Date],Today(),0),'This Month',
IF(InMonth([Termination Date],Today(),1),'Next Month',
IF(InMonth([Termination Date],Today(),2),'2 Month',
IF(InMonth([Termination Date],Today(),3),'3 Month', 'More Than 3 Months')))))as Flag
Resident
STAFF

WHERE len(trim([Termination Date]));



This will load all records, but leave the Flag field empty if the record has no value in the Termination Date field


Load
BIUNIQUEID,
IF([Termination Date] <= Today(), 'Terminated',
IF(InMonth([Termination Date],Today(),0),'This Month',
IF(InMonth([Termination Date],Today(),1),'Next Month',
IF(InMonth([Termination Date],Today(),2),'2 Month',
IF(InMonth([Termination Date],Today(),3),'3 Month', if(len(trim([Termination Date])),'More Than 3 Months'))))))as Flag
Resident
STAFF;


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert

Thanks for your solutions!

Your solutions are always precise and effective. I appreciate your great support in my journey of learning this awesome BI tool called QlikView