Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
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
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
Thanks Kiran - had to make a couple of minor tweaks, but it worked.