Segmentation with multiple data sets - Qlik Community

Report Inappropriate Content · ‎2015-01-20

Hello,

I have built a number of 'segmentations' (based on cumulative volume, margin, # of weeks sold, etc.) into my load script. This has worked well for the current data set, which represents a single 'market'.

What I now need to do is build scalability into my script so that the segmentation will take place for each 'market' that will be loaded into the model. The multiple market data will be coming from the same data source - a single Excel file.

As a simplified example, I would load something like the following:

[Volume Segmentation]:

LOAD

Market,

SKU,

Volume

from ...

I would then manipulate and restrict this data some, using WHERE clauses.

Then I would look up the segmentation with Interval Match, i.e.:

[SegmentationLookUp]:

LOAD * inline [

GreaterThan,LessThan,VolumeSegment

'$(volumeA_lower)','$(volumeA_upper)',A

'$(volumeB_lower)','$(volumeB_upper)',B

'$(volumeC_lower)','$(volumeC_upper)',C

'$(volumeD_lower)','$(volumeD_upper)',D

];

IntervalMatch:

inner Join ([Volume Segmentation]) IntervalMatch (%CumulativeVol) LOAD distinct GreaterThan, LessThan Resident [SegmentationLookUp];

Left Join ([Volume Segmentation]) LOAD * Resident [SegmentationLookUp];

drop table [SegmentationLookUp];

Drop fields GreaterThan, LessThan;

What I need to do now is carry out this type of segmentation by each market that is loaded in. Without hard-coding this in and repeating the segmentation in my script for each market in the model, is there a clever and dynamic way to tell QV to apply the segmentation scripts to each market in the model?

Thanks very much for your input!

Mel

Gabriel · ‎2015-01-21

Hi,

Am assuming [Volume Segmentation]:] is distinct.

If my assumptions is correct then I think having a loop before [SegmentationLookUp]: to loop through each [Volume Segmentation].

This should give you want you are after.

Hope this help or start up an idea

Best Regards

Report Inappropriate Content · ‎2015-01-21

Hi Gabriel,

Thanks for the input - I believe your idea is along the right lines.

Perhaps you could help with further detail, I currently have the script below, up until [Volume Segmentation] where I have commented the script out. I believe I need to add a loop for [SKUSegmentation] though I'm not sure how. I need to calculate cumulative volume for the volume of each market individually. I'm not sure if I will need this loop to produce separate tables for each market that I can concatenate later or how to approach this.

Any advice you may have would be so much appreciated, and apologies if this is a bunch of jibberish (as I'm not sure what I'm doing!)

Thanks,

M

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

[S&OPAnalyticsData.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)

//where [Week Start Date] >= '01/01/2013' AND [Week Start Date] <= '01/01/2014'

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');

//Sort volume and calculate cumulative volume column (THIS NEEDS TO BE 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];

/*