Data modeling and expression issue

[saradhi_it]

Hi

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

[Kushal_Chawda]

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

[saradhi_it]

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

[effinty2112]

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(Price*Margin/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

[MK_QSL · Accepted solution]

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;