topic Re: Can someone correct the code please in QlikView

Can someone correct the code please

Fri, 12 Jun 2015 02:10:23 GMT

Table3:

LOAD SKU#, [Sum of Units packed in recent 30 days],(TODAY() - MinDate30) AS [Diff. of days in 30 days time frame];

LOAD SKU#,Sum([Units Packed 30]) as [Sum of Units packed in recent 30 days], Min(FilteredDate30) AS MinDate30

GROUP BY SKU#;

LOAD SKU#, [Units Packed] AS [Units Packed 30],[Ship Date] AS FilteredDate30

RESIDENT Table1

WHERE [Ship Date] > TODAY()-31;

Table4:

LOAD SKU#, [Sum of Units packed in recent 30 days],[Diff. of days in 30 days time frame],([Sum of Units packed in recent 30 days]/[Diff. of days in 30 days time frame]) as [30 day velocity]

GROUP BY SKU#;

LOAD SKU#, [Sum of Units packed in recent 30 days], [Diff. of days in 30 days time frame]

Resident Table3

Re: Can someone correct the code please

Fri, 12 Jun 2015 02:27:46 GMT

its throwing an error: Aggr() function needs group by clause;

Re: Can someone correct the code please

PrashantSangle — Fri, 12 Jun 2015 04:52:34 GMT

Hi,

When you use aggregated function in script you need to use group by clause and group by non aggregated field.

For example

Data:
LOAD * INLINE [
    id, Country, Sales
    1, India, 5000
    2, UK, 1000
    4, USA, 6000
    3, Aus, 5200
];

NoConcatenate
data1:
load id,Country,Sum(Sales) as sumSales Resident Data Group by id,Country;

DROP Table Data

Regards

Re: Can someone correct the code please

jonathandienst — Fri, 12 Jun 2015 05:37:09 GMT

I dont know about correcting it, but all that code can be simplified to:

LOAD SKU#,

Sum([Units Packed]) as [Sum of Units packed in recent 30 days],

(TODAY() - Min([Ship Date])) AS [Diff. of days in 30 days time frame],

(Sum([Units Packed])/(TODAY() - Min([Ship Date]))) as [30 day velocity]

RESIDENT Table1

WHERE [Ship Date] > TODAY()-31

GROUP BY SKU#;

Re: Can someone correct the code please

Sun, 14 Jun 2015 16:49:12 GMT

It worked. Thanq Jonathan