Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Cumulative column in script using loop

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

1 Solution

Accepted Solutions

Re: Cumulative column in script using loop

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;

2 Replies

Re: Cumulative column in script using loop

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;

Not applicable

Re: Cumulative column in script using loop

Hi Ruben,

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

The second option works brilliantly, and performance seems great.

Thanks!

M