2 Replies Latest reply: Apr 11, 2011 2:49 AM by hans.nyberg RSS

    Using Dates in variables in a script

      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
          Patrick Laredo

          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