Using Dates in variables in a script

[#1]

Hello,
I am new to Qlikview, and I am now working on a script to create several QVD-files, each with one week of data. I hope someone can help me understand how dates in variables are working.

My idea was to make a loop, where I have one variable containig the first day of the week and for every loop, I increase it with 7 days. But I have not succeeded to make the variable contain a correct date.

This is the script I am running:

SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

//Define the total period I should extract data for.
let vStartDate = makedate(2011,01,01);
let vEndDate = weekstart(today()-3); //Thursdays the data is updated.
trace $(vStartDate) - $(vEndDate);

//Now prepare for running the first week
//Make sure that the first date is a Monday. I want to extract full weeks.
//Also, get the weekname for this date in the format YYYYWW.
let vWeekStart = weekstart($(vStartDate));
let vNextWeekStart = $(vWeekStart) + 7;
let vWeekName = weekyear($(vWeekStart))*100 + week($(vWeekStart));
trace week $(vWeekName) is from $(vWeekStart) to $(vNextWeekStart);

let countloops = 0; //Just during test, to be able to exit loop

//Loop through the weeks to save weekly QVD-files
do while $(vWeekStart) <= $(vEndDate)
let vWeekStart = $(vNextWeekStart);
let vNextWeekStart = $(vWeekStart) + 7;
trace week $(vWeekName) is from $(vWeekStart) to $(vNextWeekStart);

//for the test, so that I exit the loop even when dates are wrong.
let countloops = $(countloops) + 1;
exit do when $(countloops) > 3;
loop

When running this script in the debugger, I get this result:

2011-01-01 - 2011-04-04
week 190507 is from 1905-06-26 to 1880
week 190507 is from 1880 to 1887
week 190507 is from 1887 to 1894
week 190507 is from 1894 to 1901
week 190507 is from 1901 to 1908

--- Script Finished ---

While I would have expected this result instead:

2010-01-01 - 2011-04-04
week 201052 is from 2010-12-27 to 2011-01-03
week 201101 is from 2011-01-03 to 2011-01-10
week 201102 is from 2011-01-10 to 2011-01-17
week 201103 is from 2011-01-17 to 2011-01-24
week 201104 is from 2011-01-24 to 2011-01-31

--- Script Finished ---

So I don't manage to get a good date in my variables vStartWeek, vNextStartWeek nor vWeekName. How should it be written?

Thanks

Hans

[pat_agen · Accepted solution]

hi,

got these results:

2011-01-01 - 2011-04-04
week 201052 is from 2010-12-27 to 2011-01-03
week 201101 is from 2011-01-03 to 2011-01-10
week 201102 is from 2011-01-10 to 2011-01-17
week 201103 is from 2011-01-17 to 2011-01-24
week 201104 is from 2011-01-24 to 2011-01-31

--- Script Finished ---

changes to be made:

1. use the floor() command to force your dates into being seen as numbers by qlikview. thsi just makes all date maniplutaion a lot less stress free. Did this to vStartDate, vEndDate and vWeekStart

2. when you need to see these "dates" as dates - ie when using trace to see your results or when naming your qvd file - then use the date() comand.

here is your reworked script

//Define the total period I should extract data for.
let vStartDate = floor(makedate(2011,01,01));
let vEndDate = floor(weekstart(today()-3)); //Thursdays the data is updated.
let vTrace=date($(vStartDate)) & ' - ' & date($(vEndDate));
trace $(vTrace);
//trace $(vStartDate) - $(vEndDate);

//Now prepare for running the first week
//Make sure that the first date is a Monday. I want to extract full weeks.
//Also, get the weekname for this date in the format YYYYWW.
let vWeekStart = floor(weekstart($(vStartDate)));
let vNextWeekStart = $(vWeekStart) + 7;
let vWeekName = weekyear($(vWeekStart))*100 + week($(vWeekStart));
let vTrace='week ' & $(vWeekName) & ' is from ' & date($(vWeekStart)) & ' to ' & date($(vNextWeekStart));
trace $(vTrace);
//trace week $(vWeekName) is from $(vWeekStart) to $(vNextWeekStart);

let countloops = 0; //Just during test, to be able to exit loop

//Loop through the weeks to save weekly QVD-files
do while $(vWeekStart) <= $(vEndDate)
let vWeekStart = $(vNextWeekStart);
let vNextWeekStart = $(vWeekStart) + 7;

let vWeekName = weekyear($(vWeekStart))*100 + week($(vWeekStart));
let vTrace='week ' & $(vWeekName) & ' is from ' & date($(vWeekStart)) & ' to ' & date($(vNextWeekStart));
trace $(vTrace);
//trace week $(vWeekName) is from $(vWeekStart) to $(vNextWeekStart);

//for the test, so that I exit the loop even when dates are wrong.
let countloops = $(countloops) + 1;
exit do when $(countloops) > 3;
loop

by the way your vWeekName was not updating simply because you initiased it but then didn't change the value in your loop.

hope this helps

[#3]

Hello,

Thanks a lot! That got me running. I was so confused not understanding why it did not work... I also got some hints on concatenation and tracing.

Regards
Nybbe