topic Re: Looping variable script in QlikView

Looping variable script

mikegauntlett — Sat, 16 Nov 2024 03:31:55 GMT

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

Re: Looping variable script

marcus_sommer — Mon, 20 May 2019 14:27:57 GMT

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

Re: Looping variable script

mikegauntlett — Mon, 20 May 2019 14:32:54 GMT

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)

Re: Looping variable script

marcus_sommer — Mon, 20 May 2019 14:41:54 GMT

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

Re: Looping variable script

mikegauntlett — Mon, 20 May 2019 14:55:50 GMT

Hi Marcus thank you for the assistance.

However I still get 1994 as the date from,

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

Re: Looping variable script

marcus_sommer — Tue, 21 May 2019 08:03:01 GMT

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