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:33 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

Saif

Re: Creating New Calculative Fields in Load Scrip

jyothish8807 — Thu, 11 Apr 2019 06:26:34 GMT

Hi Saif,

Try this:

A:
Load
Year,
Period,
Date(Date#(Start_Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Start_Date,
Date(Date#(End_Date,'DD/MM/YYYY'),'DD/MM/YYYY') as 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
];

MinMax:
Load
min(Start_Date) as MinDate,
max(End_Date) as MaxDate
resident A;

Let vMin=peek('MinDate');
Let vMax=peek('MaxDate');

Drop table MinMax;

Calendar:
LOAD

$(vMin) + Iterno()-1 As Num,

date(weekstart(Date($(vMin) + IterNo() - 1))+1,'DD/MM/YYYY') as Date

AutoGenerate 1 While $(vMin) + IterNo() -1 <= $(vMax);

Map:
left join(A)
IntervalMatch (Date)
Load
Start_Date,
End_Date
Resident A;

Drop table Calendar;

NoConcatenate
Final:
Load
Year,
Period,
Start_Date,
End_Date,
Date,
if(Period <> Previous(Period),1,if(Period = Previous(Period),peek(Week)+1)) as Week
resident A
order by Period,Year;

Drop table A;

Re: Creating New Calculative Fields in Load Scrip

jyothish8807 — Thu, 11 Apr 2019 06:28:02 GMT

Re: Creating New Calculative Fields in Load Scrip

saifuddin — Fri, 12 Apr 2019 01:26:22 GMT

Thanks KC. Appreciate your help.