Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Segmentation with multiple data sets

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

3 Replies
Gabriel
Valued Contributor II

Re: Segmentation with multiple data sets

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

Not applicable

Re: Segmentation with multiple data sets

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

/*

[Volume Segmentation]:

noconcatenate LOAD %SegmentationMarket, %SKU, SKUVolume,

CumulativeVol,

MostRecentYear_TotalVol,

CumulativeVol/MostRecentYear_TotalVol as %CumulativeVol

resident [SKUSegmentation]

order by SKUVolume desc;

drop table [SKUSegmentation];

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

Gabriel
Valued Contributor II

Re: Segmentation with multiple data sets

Hi,

Am not sure if I follow you. Please attach sample qvw file.

Community Browser