topic Re: Logics in load scripts to calculate the numbers in QlikView

Logics in load scripts to calculate the numbers

EQ — Wed, 06 Mar 2019 08:02:27 GMT

Hi Expert,

I got a logic issue and need your suggestions and help in load script of Qlikview.

I have a Holiday table as below and another Date table, what I want is that to calculate the NumOfSatSun between the two dates of the Date Table based on the Holiday Table to generate the Final Table~

Would a kind and enlightened please advise on my error, many thanks in advance!

Holiday Table:

HolidayDate	Holiday_Year	NumOfSatSun
2017/12/25	2017	0
2017/12/30	2018	0
2018/1/1	2018	2
2018/2/15	2018	0
2018/2/21	2018	2
2018/4/5	2018	0
2018/4/7	2018	1
2018/4/29	2018	0
2018/5/1	2018	1
2018/6/16	2018	0
2018/6/18	2018	2
2018/9/22	2018	0
2018/9/24	2018	2
2018/10/1	2018	0
2018/10/7	2018	2
2018/12/25	2018	0
2018/12/30	2019	0
2019/1/1	2019	1
2019/2/3	2019	0
2019/2/10	2019	3
2019/4/5	2019	0
2019/4/7	2019	2
2019/5/1	2019	0
2019/6/7	2019	0
2019/6/9	2019	2
2019/9/13	2019	0
2019/9/15	2019	2
2019/10/1	2019	0
2019/10/7	2019	2
2019/12/25	2019	0

DateTable

StartDate	EndDate
2017/12/13	2018/2/7
2017/12/14	2018/2/23
2018/9/21	2018/10/10
2019/1/16	2019/2/12
2019/1/23	2019/2/21

Final Table:

StartDate	EndDate	NumOfSatSun
2017/12/13	2018/2/7	2
2017/12/14	2018/2/23	4
2018/9/21	2018/10/10	4
2019/1/16	2019/2/12	4
2019/1/23	2019/2/21	3

Re: Logics in load scripts to calculate the numbers

Vegar — Wed, 06 Mar 2019 08:24:49 GMT

You need to implement intervalmatch.

LOAD 
     HolidayDate, 
     Holiday_Year, 
     NumOfSatSun
FROM
[https://community.qlik.com/t5/QlikView-App-Development/Logics-in-load-scripts-to-calculate-the-numbers/m-p/1552748#M440397]
(html, codepage is 1252, embedded labels, table is @1);

IntervalMatch(HolidayDate)
LOAD 
     StartDate, 
     EndDate
FROM
[https://community.qlik.com/t5/QlikView-App-Development/Logics-in-load-scripts-to-calculate-the-numbers/m-p/1552748#M440397]
(html, codepage is 1252, embedded labels, table is @2);

Re: Logics in load scripts to calculate the numbers

Vegar — Wed, 06 Mar 2019 08:30:12 GMT

Attaching an sample QVW file that reads your post tables

Re: Logics in load scripts to calculate the numbers

EQ — Wed, 06 Mar 2019 09:53:39 GMT

Hi Vegar,

You genius! That is really I want, thanks!

Eric