topic Re: Data modeling and expression issue in QlikView

Data modeling and expression issue

saradhi_it — Thu, 13 Jul 2017 08:48:45 GMT

i had requirement it was about sale in store the sale should be calculated based on bookings like in levels

SORRY folks in my previous post i forgot to add price column in sample data without the price column product margin can't be calculated

Range level

0	25	Level1
25	50	Levjeel2
50+		Level3

the margin should be caluclated based on the sale of first 25 bookings ,if sale crosses 25- 50 it will come in level 2 if it crosses 50+ it will come under level 3 , the bookings should be calculated based the time stamp only like FIrst in First serve

the difficulties i face data modelling based on the time stamp i need pick 0-25 sale if it crossed 25+ it will be in level 2 then 50+ will be in level3 for example if the product sale was 150 for a single day then count of the total bookings should be consider as per level category like

level level 1= 25(1-25 ),( count of products,level category)

level 2= 25(26-50)

then level 3 =100(51-150)

if the product sale was 75 per day then bookings will fall in this category

level level 1= 25(1-25 ),( count of products,level category)

level 2= 25(26-50)

then level 3 =25(51-150)

and product margin should be calculated based on the level

example= the product 1 total sale in a day 15 it will come under level 1 category

product margin =28*2/100 (sum(price*margin) ( product margin prices are available in excel as per the level category)

sample data and product margin was attached

the product margin file was attached , the source of the file was excel i'm unable to build the data modelling also

PFA

the out put required by the user

date orderid product margin level category

5-10-17 12334567 0.56 Level 1

NOTE: ITS SAMPLE DATA I ATTACHED AS PER THE REQUIREMENT YOU CAN ADD THE DATA TO FULFILL ALL LEVEL CATEGORYS IN IT BUT THE OUTPUT SHOULD BE LIKE THE ABOVE ONE

Regards

Pardhu

Message was edited by: pardha saradhi s

Re: Data modeling and expression issue

Kushal_Chawda — Thu, 13 Jul 2017 09:35:36 GMT

Level1:

CrossTable(Date,Margine)

LOAD Level1 as Product,

[42005],

[420051],

[42006],

[42007],

[42008],

[42009]

FROM

(ooxml, embedded labels, table is Level1);

Levels:

NoConcatenate

LOAD Product,

Margine,

autonumber(Product&date(left(trim(Date),5))) as Key,

date(left(trim(Date),5)) as Date,

'Level1' as Level

Resident Level1;

DROP Table Level1;

Level2:

CrossTable(Date,Margine)

LOAD Level2 as Product,

[42005],

[420051],

[42006],

[42007],

[42008],

[42009]

FROM

(ooxml, embedded labels, table is Level2);

Concatenate(Levels)

LOAD Product,

Margine,

autonumber(Product&date(left(trim(Date),5))) as Key,

date(left(trim(Date),5)) as Date,

'Level3' as Level

Resident Level2;

DROP Table Level2;

Level3:

CrossTable(Date,Margine)

LOAD Level3 as Product,

[42005],

[420051],

[42006],

[42007],

[42008],

[42009]

FROM

(ooxml, embedded labels, table is Level3);

Concatenate(Levels)

LOAD Product,

Margine,

autonumber(Product&date(left(trim(Date),5))) as Key,

date(left(trim(Date),5)) as Date,

'Level3' as Level

Resident Level3;

DROP Table Level3;

Data:

LOAD date(floor(Date)) as ActualDate,

autonumber(Products&date(floor(Date))) as Key,

orderid,

Products

FROM

(ooxml, embedded labels, table is Sheet2);

Re: Data modeling and expression issue

saradhi_it — Thu, 13 Jul 2017 11:20:16 GMT

sorry KUSHAL

i forgot to add the price column in the sample data without price margin can't be calculated , the count (product) can be used to know the product will fall in which category

for example

on 6-10-17 , 16 products (product1) was sold and the it will be in level1

and margin will be calculated 28*2/2 the margin will be

Re: Data modeling and expression issue

effinty2112 — Fri, 14 Jul 2017 08:19:57 GMT

Hi Pardha,

Try:

Data:

LOAD

Date(Floor(Date)) as Date,

orderid,

Price,

Capitalize(Products) as Product //product2 => Product2

FROM

test_data.xlsx

(ooxml, embedded labels, table is Sheet2);

AggrOverDay:

LOAD

Ceil(Rangemin(Qty,51)/25) as Level;

LOAD

Date,

Count(orderid) as Qty,

Product,

Price

Resident Data

Group by Date, Product,Price;

for L = 1 to 3

CTMargins:

LOAD Date(Level$(L)) as Date,

'$(L)' as Level,

Product1,

Product2,

Product3

FROM

test_data.xlsx

(ooxml, embedded labels, table is Level$(L), filters(

Transpose()

));

Next L;

Margins:

CrossTable (Product, Margin,2) LOAD * Resident CTMargins;

Left Join(AggrOverDay)

LOAD * Resident Margins;

drop Tables Margins, CTMargins;

Left Join(Data)

LOAD * Resident AggrOverDay;

DROP Table AggrOverDay;

Now we can get this straight table (first 10 rows):

Date	orderid	Level	*sum(PriceMargin/100)**
05/10/2016	12334567	1	0.56
05/10/2016	12334568	1	0.96
05/10/2016	12334569	1	1.7
05/10/2016	12334570	1	0.96
05/10/2016	12334571	1	0.96
05/10/2016	12334572	1	0.96
05/10/2016	12334573	1	0.96
05/10/2016	12334574	1	0.96
05/10/2016	12334575	1	0.96
05/10/2016	12334576	1	0.96

Cheers

Andrew

Re: Data modeling and expression issue

MK_QSL — Sat, 15 Jul 2017 12:01:00 GMT

LevelRange:
Load * Inline
[
From, To, Level
0, 25, Level1
26, 50, Level2
51, 99999, Level3
];


Temp_Level1:
CrossTable(Date, Value,2)
LOAD Level1 as Product,
'Level1' as Level,
     [42648], 
     [42649], 
     [42650], 
     [42651], 
     [42652], 
     [42653]
FROM
Pardhu.xlsx
(ooxml, embedded labels, table is Level1);


CrossTable(Date, Value,2)
LOAD Level2 as Product,
'Level2' as Level,
     [42648], 
     [42649], 
     [42650], 
     [42651], 
     [42652], 
     [42653]
FROM
Pardhu.xlsx
(ooxml, embedded labels, table is Level2);


CrossTable(Date, Value,2)
LOAD Level3 as Product,
'Level3' as Level,
     [42648], 
     [42649], 
     [42650], 
     [42651], 
     [42652], 
     [42653]
FROM
Pardhu.xlsx
(ooxml, embedded labels, table is Level3);


Map_Level2:
Mapping Load
Product & '|' & Level & '|' & Date as Key,
Value 
;
Load 
Capitalize(Product) as Product,
Level,
Date(Num#(Date,'#0')) as Date,
Num(Value,'#0') as Value
Resident Temp_Level1;


Drop Table Temp_Level1;


Data:
LOAD TimeStamp(Date) as DateTime,
Date(Floor(Date)) as Date,
Time(Frac(Date)) as Time, 
     orderid, 
     Price, 
     Capitalize(Products) as Product
FROM
Pardhu.xlsx
(ooxml, embedded labels, table is [SAMPLE DATA]);


Temp1:
Load 
*,
AutoNumber(DateTime,Date) as TotalOrderCount
// AutoNumber(DateTime & Product,Product & Date) as TotalOrderCount
Resident Data;


Drop Table Data;


Inner Join
IntervalMatch(TotalOrderCount)
Load From, To Resident LevelRange;


Left Join (Temp1)
Load * Resident LevelRange;


DROP Table LevelRange;


Drop Field From, To;


Temp2:
Load *, Price * ApplyMap('Map_Level2',Product & '|' & Level & '|' & Date)/100 as ProductMargin Resident Temp1;


Drop Table Temp1;