Looping variable script

mikegauntlett · ‎2019-05-20

Hi Guys,

If i wanted to make vWeekStart = every Monday from 2019-01-01, how would I achieve this ?

I have the below so far

let vStartDate =(2019-01-07);
let vEndDate = weekstart(today());
let vWeekStart = weekstart($(vStartDate));
let vNextWeekStart = $(vWeekStart) + 7;
let countloops = 0; //Just during test, to be able to exit loop
do while $(vWeekStart) <= $(vEndDate)
let vWeekStart = $(vNextWeekStart);
let vNextWeekStart = $(vWeekStart) + 7;
let vWeekName = weekyear($(vWeekStart))*100 + week($(vWeekStart));
let countloops = $(countloops) + 1;
exit do when $(countloops) > 3;
Loop

Currently I get 1901 as an output.

I need the format to be yyyy-MM-dd

Many Thanks

marcus_sommer · ‎2019-05-20

For me it's not clear what do you tries to do. I suggest to rethink the whole approach with all the variables and to use instead a master-calendar and create there all needed fields respectively flags. More background could you find here: How-to-use-Master-Calendar-and-Date-Values.

- Marcus

mikegauntlett · ‎2019-05-20

Hi Marcus,

I need to be able to load data each Monday from the qvd file that gets created weekly (Monday).

I can do this through a concatenated load but then I have to add to the script every week.

I was trying to set up a variable in order to replace part of the Lib: and have them load every week. so week 1 i will have one data set week 52 52 data sets from each Monday.

FROM [lib://CLM Snapshots (Weekly)/REP_Work_Order_$(vWeekStart).qvd](qvd)

marcus_sommer · ‎2019-05-20

If you want to load always the file from the last monday you need just one variable which could be defined directly without any loop by using something like this: date(weekstart(today()), Format)

- Marcus

mikegauntlett · ‎2019-05-20

Hi Marcus thank you for the assistance.

However I still get 1994 as the date from,

let vWeekStart = weekstart (date(today(),'YYYY-MM-dd'));

marcus_sommer · ‎2019-05-21

Your approach and my suggestion isn't the same because you apply the weekstart as the most outer function and they will return a timestamp with the your default-format of a timestamp (the formatting in between will be meaningless).

Further your chosen formatting is not unproblematic because as far as your variable is expanded/evaluated a content like:

let vStartDate =(2019-01-07);

returned just 2011 because the "-" char will be handled as operator and it will be calculate the content.

This means your file-path might be look like:

FROM [lib://CLM Snapshots (Weekly)/REP_Work_Order_vWeekStart.qvd] (qvd);

and sometimes it's also useful/necessary to build the whole file-path within a variable like:

let vFilePath = '[lib://CLM Snapshots (Weekly)/REP_Work_Order_' & date(weekstart(today()), 'YYYY-MM-dd') & '.qvd]';

and then using:

FROM$(vFilePath) (qvd);

- Marcus

dates

Loop

Looping variable script

variable