topic Using Dates in variables in a script in QlikView

Using Dates in variables in a script

Fri, 08 Apr 2011 13:41:31 GMT

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

Using Dates in variables in a script

pat_agen — Fri, 08 Apr 2011 14:25:08 GMT

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

Using Dates in variables in a script

Mon, 11 Apr 2011 06:49:23 GMT

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