Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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??
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
Can you share the script that you currently have?
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
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
Thanks Sunny,
It's working...
One more thing.. how to exclude public holidays in that?
Thanks in advance
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
Thanks Sunny,
i sorted out..