topic Re: Creating New Calculative Fields in Load Scrip in QlikView

Creating New Calculative Fields in Load Scrip

saifuddin — Sat, 16 Nov 2024 03:53:36 GMT

Hi All,

Part of a bigger project I am working on, I have to figure out numbers of week between Start and End date. From below screenshot, for each Start_date to End_date I want to figure out weeks in 7 days block. I tried using nested If statement as per below. But, it does not satisfy my need. In a nutshell I want to create two new fields as per the Desire screenshot below -- Weeks and Dates

Will appreciate your advice.

Thanks

Sai

Re: Creating New Calculative Fields in Load Scrip

jyothish8807 — Thu, 11 Apr 2019 07:23:00 GMT

Solution suggested here:

https://community.qlik.com/t5/QlikView-Scripting/Creating-New-Calculative-Fields-in-Load-Scrip/m-p/1567932#M94811

Re: Creating New Calculative Fields in Load Scrip

sunny_talwar — Thu, 11 Apr 2019 11:22:40 GMT

Here is another option for you to try

Table:
LOAD *,
	 IterNo() as Week,
	 Date(Start_Date + ((IterNo()-1)*7)) as Dates
While Start_Date + ((IterNo()-1)*7) < End_Date;
LOAD * INLINE [
    Year, Period, Start_Date, End_Date
    2017, 1, 20/2/2017, 19/05/2017
    2017, 2, 19/06/2017, 15/09/2017
    2017, 3, 16/10/2017, 19/01/2018
    2018, 1, 19/02/2019, 1805/2018
    2018, 2, 18/06/2018, 14/09/2018
    2018, 3, 15/10/2018, 25/01/2019
    2019, 1, 25/02/2019, 24/05/2019
    2019, 2, 24/06/2019, 20/09/2019
    2019, 3, 21/10/2019, 24/01/2020
    2020, 1, 24/02/2020, 22/05/2020
    2020, 2, 22/06/2020, 18/09/2020
    2020, 3, 19/10/2020, 22/01/2021
];

Re: Creating New Calculative Fields in Load Scrip

saifuddin — Fri, 12 Apr 2019 01:27:58 GMT

Thanks Sunny. Quick and easy solution. Appreciate your help.