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: 
martin_hamilton
Creator
Creator

Evaluating a generated date as part of load script?

Hi

I am calculating a date based on 2 values being passed into load script below:

   

Sub_Contract_Start_Date,

Sub_Contract_Term,

AddMonths ([Sub_Contract_Start_Date],[Sub_Contract_Term]) as [Sub_Contract_End_Date]

What I want to also do is identify any of the dates I create as part of the load which are within the next 30 days i.e. the date created which is named Sub_Contract_End_Date and give them an indicator e.g. contract expiring in next 30 days.

Is it possible to do this within the same load script or do I need to create a resident table to do the calculation?

I was thinking extending the expression with an IF statement but wasnt sure whether that was possible.

Any help appreciated.

thanks

Martin

1 Solution

Accepted Solutions
sunny_talwar

You should be able to do this in the same load statement, but I am just not sure what is this getting compared to? Today? may be this

Sub_Contract_Start_Date,

Sub_Contract_Term,

AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) as [Sub_Contract_End_Date],

If(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today() <= 30, 1, 0) as ContractExpiringFlag

View solution in original post

2 Replies
sunny_talwar

You should be able to do this in the same load statement, but I am just not sure what is this getting compared to? Today? may be this

Sub_Contract_Start_Date,

Sub_Contract_Term,

AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) as [Sub_Contract_End_Date],

If(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today() <= 30, 1, 0) as ContractExpiringFlag

martin_hamilton
Creator
Creator
Author

Hi Sunny this worked great for me, made some amends to your original solution which now allows me to generate a contract status based on the data.

If(Interval(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today()) >0 and Interval(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today()) <30, 'Contract Ending',if(AddMonths([Sub_Contract_Start_Date], [Sub_Contract_Term]) - Today() >30,'In Contract','Out Of Contract')) as Sub_Contract_Status

I think i have it right but many thanks for the steer.

cheers

Martin