topic Re: Group By Load Script With Formula in QlikView

Group By Load Script With Formula

rileymd88 — Tue, 26 Jan 2016 22:34:47 GMT

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

Re: Group By Load Script With Formula

sunny_talwar — Tue, 26 Jan 2016 23:03:06 GMT

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

Re: Group By Load Script With Formula

tamilarasu — Wed, 27 Jan 2016 01:42:26 GMT

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:

Re: Group By Load Script With Formula

rileymd88 — Tue, 16 Feb 2016 13:30:47 GMT

There was indeed a problem with the date field which was causing the duplicates. Thanks for your help!