topic Re: Exclude weekends in for loop load script in QlikView

Exclude weekends in for loop load script

ranganadh1979 — Sat, 16 Nov 2024 02:08:42 GMT

Hi All,

May i know how to exclude the weekends in my for loop load script in qlikview?

I have 2 date variables. start date and end date. Start date is static. I am loading the data per day by using for loop and storing in a qvd based on end date variable.

i want to load only work days(monday to friday) not weekends(Saturday and Sunday) in my for loop.

Any suggestions??

Re: Exclude weekends in for loop load script

sunny_talwar — Wed, 23 Oct 2019 11:26:57 GMT

Can you share the script that you currently have?

Re: Exclude weekends in for loop load script

ranganadh1979 — Wed, 23 Oct 2019 12:17:41 GMT

Hi Sunny,

Thanks for your reply. please find the below script.

let vDatefrom=Floor('2019-09-30');//Floor(yearstart(Today()));
let vDateTo=Floor(Today());

let vApiStartDate= date($(vDatefrom),'DD%20MMMM%20YYYY');

for i=$(vDatefrom) to $(vDateTo)

let vApiEndDate= date($(i),'DD%20MMMM%20YYYY');

Fact_Table:
LOAD *

FROM
[http://***/***/Report?api-key=***&reportname=Complete%20TB%20Report&headers=TRUE&arg1=$(vApiStartDate)&arg2=$(vApiEndDate)&response-type=csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate
LOAD *

FROM
[C:\UsersQVD\Full_Load.qvd](qvd);

store Fact_Table into C:\UsersQVD\Full_Load.qvd (qvd);

drop table Fact_Table;

next i;

I dont want to load all dates in the for loop. i want to load only week days data and append into qvd.

Thanks in advance

Re: Exclude weekends in for loop load script

sunny_talwar — Wed, 23 Oct 2019 12:31:03 GMT

Try this

LET vDatefrom = Num(MakeDate(2019, 9, 30));//Floor(yearstart(Today())); LET vDateTo = Num(Today()); LET vApiStartDate = Date($(vDatefrom),'DD%20MMMM%20YYYY'); FOR i = $(vDatefrom) to $(vDateTo) LET vApiEndDate = Date($(i),'DD%20MMMM%20YYYY'); LET vDay = WeekDay($(i)); If '$(vDay)' <> 'Sun' and '$(vDay)' <> 'Sat' THEN LOAD '$(vApiEndDate)' as ApiEndDate, '$(vDay)' as WeekDay AutoGenerate 1; ENDIF NEXT

Re: Exclude weekends in for loop load script

ranganadh1979 — Thu, 24 Oct 2019 09:05:37 GMT

Thanks Sunny,

It's working...

One more thing.. how to exclude public holidays in that?

Thanks in advance

Re: Exclude weekends in for loop load script

sunny_talwar — Thu, 24 Oct 2019 10:48:03 GMT

Each country have it's own public holiday. and sometimes the list changes based on what you observe... you need to supply the list and then it can be done

Re: Exclude weekends in for loop load script

ranganadh1979 — Thu, 24 Oct 2019 15:34:25 GMT

Thanks Sunny,

i sorted out..