Load Script - create new column values based on existing date columns

[martin_hamilton]

Hi

I have a data table I am loading whereby I am first creating a new column which takes the Contract Start Date PLUS the Contract Tenure.

What I wish to do is look at this new date column and create a brand new column which states whether the customer is 'In Contract' or 'Out of Contract'. This would be based on whether the date was before or after the date of the load (I have created a variable vToday which I think I can incorporate.

I cant find anything in community which points me in the right direction.

Any advice/solutions out there?

Thanks

Martin

Message was edited by: Martin Hamilton Added as a sample

[Siva_Sankar]

Martin,

Need a sample to show also not understanding whether load date is column which is already exsting in your data model.

You may create a load date (dashboard refresh date) using below

Date( floor(ReloadTime()),'DD/MM/YYYY') as  LoadDate

then  create an if statement using the above date to generate in contract column and out of contract column

Siva

[MindaugasBacius]

Use IF statement in your data model like:

Load

     [Contract Start Date]

     ,[Contract Tenure]

     ,IF(Today() <= [Contract Start Date] + [Contract Tenure], 'In Contract', 'Out of Contract') as [Is in contract]

From Source;

[martin_hamilton]

Hi Mindaugas - I have added the logic you provided into my load script but it doesnt seem to be taking into consideration the actual contract end date but looks at the contact start date. Can you take a look and see where I am going wrong? Because I am creating a new contract end date i was sure whether it was possible to produce a look up as part of the load to generate the new column?

Thanks

[martin_hamilton]

Hi Siva - I have uploaded a sample which incorporates the suggested approach from Mindaugas.

[#6 · Accepted solution]

Martin,

I noticed in your load script you used AddMonths to add the Contract Tenure to the Contract Start Date. I believe you need to do the same when using the formula from Mindaugas.

So, update the formula to be:

IF(Today() <= AddMonths ([Contract Start Date],[Contract Tenure]) , 'In Contract', 'Out of Contract') as [Is in contract]

From Source

Without the AddMonths, the formula is probably adding the Tenure as days instead of months.

Hope this helps!

- Stan

[martin_hamilton]

Thats exactly what it was doing! thanks Stan