Cumulative column in script using loop

Wed, 21 Jan 2015 15:19:59 GMT

Hello,

I am trying to create a table of products with a cumulative volume column. I have done this in my script successfully for the entire loaded data set, but now need to modify my script to account for multiple markets. The cumulative volume column should be per market, not based on overall volume.

This is my script so far, though I am struggling with the correct syntax for the loop (red font below), please can anyone advise?

//Load in all volume

[SKU Segmentation-1]:

LOAD

[Market] as %SegmentationMarket,

[SKU Number] as %SKU,

sum([Actual Volume (HL) W+1]) as [SKUVolume-1],

[Week Start Date]

FROM

[PlanningAnalyticsData.xlsm]

(ooxml, embedded labels, table is [Demand - W+1])

WHERE [Actual Volume (HL) W+1] >='0' AND ([Actual Volume (HL) W+1] <> '0' OR [Forecasted Volume (HL) W+1] <> '0')

group by [Market], [SKU Number], [Week Start Date]

;

//Define 52 week parameter

Temp:

LOAD max([Week Start Date])-365 as mini, max([Week Start Date]) as maxi resident [SKU Segmentation-1];

Let vMaxWeekStartDateLessOneYear = peek('mini',0,'Temp');

Let vMaxWeekStartDate = peek('maxi',0,'Temp');

drop table Temp;

//Total volume per market

[Market List]:

LOAD distinct

%SegmentationMarket,

sum([SKUVolume-1]) as [MostRecentYear_TotalVol_Master]

resident [SKU Segmentation-1]

where [Week Start Date] >= $(vMaxWeekStartDateLessOneYear) AND [Week Start Date] <= $(vMaxWeekStartDate)

group by %SegmentationMarket;

Market_to_TotalVol:

MAPPING LOAD

[%SegmentationMarket],

[MostRecentYear_TotalVol_Master]

resident

[Market List];

//Restrict volume to last 52 weeks

[SKU Segmentation]:

LOAD %SegmentationMarket,

%SKU,

sum([SKUVolume-1]) as SKUVolume,

ApplyMap('Market_to_TotalVol',%SegmentationMarket,null()) as [MostRecentYear_TotalVol]

resident [SKU Segmentation-1]

where [Week Start Date] >= $(vMaxWeekStartDateLessOneYear) AND [Week Start Date] <= $(vMaxWeekStartDate)

group by %SegmentationMarket, %SKU;

drop table [SKU Segmentation-1];

[MarketList]:

load

%SegmentationMarket

resident [Market List];

drop table [Market List];

for vMarketNo = 1 to NoOfRows('MarketList')

let vMarketName = peek(%SegmentationMarket,vMarketNo-1,'MarketList');

//Create cumulative column for descending volume, per market

[SKUSegmentation]:

noconcatenate LOAD

%SegmentationMarket,

%SKU,

SKUVolume,

RangeSum(SKUVolume, Peek('CumulativeVol')) as CumulativeVol,

MostRecentYear_TotalVol

resident [SKU Segmentation]

where %SegmentationMarket = '$(vMarketName)'

order by SKUVolume desc;

next vMarketNo

drop table [SKU Segmentation];

Re: Cumulative column in script using loop

rubenmarin — Wed, 21 Jan 2015 16:02:07 GMT

Hi, If you load SKUSegmentation with 'NoConcatenate' it will create a table for each different vMarketNo.

Before the bucle you can create an (almost) empty inline table and concatente values in this table:

SKUSegmentation: LOAD * Inline [DumbField];

For...

...

SKUSegmentation:

Concatenate (SKUSegmentation)

LOAD ...

Also, I usually do this kind of cummulative sum without a bucle, I use a order by clause and peek() to check when a new market starts, I don't know wich one gets better performance:

SKUSegmentation:

LOAD ...

If(Peek(%SegmentationMarket)=%SegmentationMarket, RangeSum(SKUVol...), SKUVolume) as CumulativeVol,

....

order by %SegmentationMarket, SKUVolume desc;

Re: Cumulative column in script using loop

Wed, 21 Jan 2015 17:18:16 GMT

Hi Ruben,

Thank you very much for the response - incredibly helpful and timely!

The second option works brilliantly, and performance seems great.

Thanks!

topic Re: Cumulative column in script using loop in QlikView

Cumulative column in script using loop

Re: Cumulative column in script using loop

Re: Cumulative column in script using loop