Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Qlikview loop - Help

Need some help with a loop statement in QV - I'm not the best coder as you can probabaly tell from below.

I've got multiple country data in my QVD file which comes into my DW at different times.  So in July, I might have Jan-Mar for UK compared to Jan-May for France.  So to get a proper comparison to prior year I want to ensure I compare the same periods:

Jan-Mar 2011 to Jan-Mar 2012 for the UK

Jan-May 2011 to Jan-May 2012 for France

I'm trying to get the attached output using the code below, but getting nowhere fast....help!  (Only included some code)

Screen shot 2012-07-13 at 17.31.47.png

//LOAD DISTINCT LIST OF COUNTRIES

LOAD [BU-Entity Code] as [vBU-Entity Code]

RESIDENT [Country];

for each [BU-Entity Code] in [vBU-Entity Code]

SET vBU = fieldvalue([BU-Entity Code],1);

LOAD

     [BU-Entity Code] &'-'& date(MAX([$PERIOD]))          as [$SETID_PERIOD],

     'Latest Mth (CY)'                                    as [Date- MAT Period]

Resident [Sales Data]

Where [BU-Entity Code] = $(vBU);

Next [BU-Entity Code]

Tags (4)
1 Solution

Accepted Solutions
Not applicable

Re: Qlikview loop - Help

Try:

TableName:

LOAD [BU-Entity Code] as vBUEntityCode

RESIDENT [Country];

for a = 1 to NoOfRows('TableName')-1

vBU = peek('vBUEntityCode',$(a),'TableName');

ConcatLoadTable:

LOAD [BU-Entity Code] &'-'& date(MAX([$PERIOD])) as [$SETID_PERIOD],

     'Latest Mth (CY)' as [Date- MAT Period]

Resident [Sales Data]

Where [BU-Entity Code] = '$(vBU)';

Next

Drop table TableName;

Regards,

Kiran Rokkam

2 Replies
Not applicable

Re: Qlikview loop - Help

Try:

TableName:

LOAD [BU-Entity Code] as vBUEntityCode

RESIDENT [Country];

for a = 1 to NoOfRows('TableName')-1

vBU = peek('vBUEntityCode',$(a),'TableName');

ConcatLoadTable:

LOAD [BU-Entity Code] &'-'& date(MAX([$PERIOD])) as [$SETID_PERIOD],

     'Latest Mth (CY)' as [Date- MAT Period]

Resident [Sales Data]

Where [BU-Entity Code] = '$(vBU)';

Next

Drop table TableName;

Regards,

Kiran Rokkam

Not applicable

Re: Qlikview loop - Help

Thanks Kiran - had to make a couple of minor tweaks, but it worked.

Community Browser