Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am having difficulties with the Group By Clause. I am taking a table with daily data which is mapped to a month and trying to aggregate it on the month level and do some basic calculations, however whenever I do this my straight table will give me duplicate rows as the Inventory Turns field will show up multiple times for the SKU (should only show up once per month since SKU is a unique indicator). Here is my script:
secondTable:
LOAD
[Market],
[Month],
[SKU],
[Model],
[Supplier],
Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3]) as [Total Inv],
Sum([Sales]) as [Total Sales],
Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3]) / Sum([Sales] as [Inventory Turns]
Resident firstTable Group by [Market], [Month], [SKU], [Model], [Supplier];
Here is the firstTable:
Market | Month | Day | SKU | Model | Supplier | Inv1 | Inv2 | Inv3 | Sales |
Canada | Jan-16 | 01/01/2016 | ABC | Model 1 | Supplier 1 | 18 | 19 | 17 | 18 |
Canada | Jan-16 | 02/01/2016 | ABC | Model 1 | Supplier 1 | 13 | 12 | 17 | 17 |
Canada | Jan-16 | 03/01/2016 | ABC | Model 1 | Supplier 1 | 11 | 17 | 15 | 11 |
Canada | Jan-16 | 04/01/2016 | ABC | Model 1 | Supplier 1 | 19 | 20 | 15 | 15 |
Canada | Jan-16 | 05/01/2016 | ABC | Model 1 | Supplier 1 | 13 | 20 | 20 | 20 |
Here would be the expected result of secondTable:
Market | Month | Day | SKU | Model | Supplier | Total Inv | Total Sales | Inventory Turns |
Canada | Jan-16 | 01/01/2016 | ABC | Model 1 | Supplier 1 | 246 | 81 | 3.037037 |
Is Month a field calculated in script? It may look Jan-16, but may include different dates for different rows.
I would try this:
secondTable:
LOAD
[Market],
MonthName(Date#([Month], 'MMM-YY')) as [Month],
[SKU],
[Model],
[Supplier],
Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3]) as [Total Inv],
Sum([Sales]) as [Total Sales],
Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3]) / Sum([Sales] as [Inventory Turns]
Resident firstTable
Group by [Market], MonthName(Date#([Month], 'MMM-YY')), [SKU], [Model], [Supplier];
Is Month a field calculated in script? It may look Jan-16, but may include different dates for different rows.
I would try this:
secondTable:
LOAD
[Market],
MonthName(Date#([Month], 'MMM-YY')) as [Month],
[SKU],
[Model],
[Supplier],
Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3]) as [Total Inv],
Sum([Sales]) as [Total Sales],
Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3]) / Sum([Sales] as [Inventory Turns]
Resident firstTable
Group by [Market], MonthName(Date#([Month], 'MMM-YY')), [SKU], [Model], [Supplier];
Hi Riley,
Check the below script
FirstTable:
Load *, MonthStart(Date#(Day,'DD/MM/YYYY')) as MonthStart;
LOAD * INLINE [
Market, Month, Day, SKU, Model, Supplier, Inv1, Inv2, Inv3, Sales
Canada, Jan-16, 01/01/2016, ABC, Model 1, Supplier 1, 18, 19, 17, 18
Canada, Jan-16, 02/01/2016, ABC, Model 1, Supplier 1, 13, 12, 17, 17
Canada, Jan-16, 03/01/2016, ABC, Model 1, Supplier 1, 11, 17, 15, 11
Canada, Jan-16, 04/01/2016, ABC, Model 1, Supplier 1, 19, 20, 15, 15
Canada, Jan-16, 05/01/2016, ABC, Model 1, Supplier 1, 13, 20, 20, 20
];
SecondTable:
LOAD
[Market],
[Month],
[MonthStart],
[SKU],
[Model],
[Supplier],
Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3]) as [Total Inv],
Sum([Sales]) as [Total Sales],
(Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3])) / Sum([Sales]) as [Inventory Turns]
Resident FirstTable Group by [Market], [Month], [MonthStart], [SKU], [Model], [Supplier];
DROP Table FirstTable;
Ouput:
There was indeed a problem with the date field which was causing the duplicates. Thanks for your help!