topic Re: Load Script - create new column values based on existing date columns in QlikView

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

martin_hamilton — Sun, 04 Sep 2016 20:20:09 GMT

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

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

Siva_Sankar — Mon, 05 Sep 2016 02:54:30 GMT

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

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

MindaugasBacius — Mon, 05 Sep 2016 05:22:04 GMT

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;

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

martin_hamilton — Mon, 05 Sep 2016 19:12:43 GMT

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

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

martin_hamilton — Mon, 05 Sep 2016 19:13:27 GMT

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

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

Tue, 06 Sep 2016 02:18:16 GMT

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

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

martin_hamilton — Tue, 06 Sep 2016 08:58:04 GMT

Thats exactly what it was doing! thanks Stan