topic Re: Qlikview loop - Help in QlikView

Qlikview loop - Help

Fri, 13 Jul 2012 16:45:37 GMT

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)

//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]

Re: Qlikview loop - Help

Fri, 13 Jul 2012 17:13:44 GMT

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

Re: Qlikview loop - Help

Fri, 13 Jul 2012 19:12:48 GMT

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