Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Can someone correct the code please

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Can someone correct the code please

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
Not applicable

Re: Can someone correct the code please

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

Re: Can someone correct the code please

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

Great dreamer's dreams never fulfilled, they are always transcended.
MVP
MVP

Re: Can someone correct the code please

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Can someone correct the code please

It worked. Thanq Jonathan

Community Browser