topic Re: loop by period value in QlikView

loop by period value

Wed, 25 Nov 2020 16:16:04 GMT

Hi, I'm new here.

Could you please help me with a script for loop and the required variables?

I have a table with period data and i need to create a file for each period:

Table:

Load

Period

From test.qvd

Period is in form 201608 and the files need to be named: test_year_month.qvd

The result tables should be e.g.:

Table2:

A+B,

Period

From test_2016_08.qvd

Once I have files for each period I need to modify the script so that it runs only for the last three periods.

Many thanks for help guys

Tommy

Re: loop by period value

Gysbert_Wassenaar — Tue, 30 Aug 2016 11:35:49 GMT

Perhaps like this:

Periods:

LOAD Period FROM test.qvd (qvd);

LET vPeriodCount = FieldValueCount('Period','Periods');

FOR i = 1 TO $(vPeriodCount)

LET vPeriod = FieldValue('Period', $(i));

LET vMonth = Right('$(vPeriod)',2);

LET vYear = Left('$(vPeriod)',4);

Temp:

LOAD

A, B, A+B, Period

FROM

test.qvd (qvd)

WHERE

Period = $(vPeriod);

STORE TABLE Temp INTO [test_$(vYear)_$(vMonth)] (qvd) ;

DROP TABLE Periods;

Re: loop by period value

Tue, 30 Aug 2016 13:32:34 GMT

Thanks so much.

Almost good, but one thing is still wrong. For the first QVD it is fine - only first period is taken. In the next QVD there are two periods, in the third - 3 and so on...

Can you please help?

Tommy

Re: loop by period value

Gysbert_Wassenaar — Tue, 30 Aug 2016 14:02:09 GMT

Oops, forgot to drop the table Temp after storing it. Add a line below the store:

DROP TABLE Temp;