Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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);
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
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
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;