topic Re: Preceding Load in QlikView

Preceding Load

Mon, 10 Feb 2014 11:16:27 GMT

Hi all,

I don't understand this script with the GROUP BY clause.

LOAD
OrderID,

ProductID,
Product,
CustomerID,
EmployeeID,
OrderDate,
Year(OrderDate) as Year,
'Q'&Ceil(Month(OrderDate)/3) as Quarter,
Month(OrderDate) as Month,
MonthName(OrderDate) as MonthYear,
Week(OrderDate) as Week,
Margin,
Cost,
Quantity,
Sales,
Sum(Sales-Margin)/Sum(Margin) as %Margin,
Sum(Sales) as TotalSales
FROM (biff, embedded labels, table is Orders$)
GROUP BY OrderID, ProductID, Product, CustomerID, EmployeeID, OrderDate, Margin, Cost, Quantity, Sales;

Is it possible to create a preceding load with group by clause like :

LOAD

Sum(Sales) as TotalSales,

Sum(Sales-Margin)-1 as %Margin

GROUP BY OrderID, ProductID, Product, CustomerID, EmployeeID, OrderDate

;

LOAD
OrderID,

Re: Preceding Load

Mon, 10 Feb 2014 11:23:50 GMT

Hi Joshua

I don't think you can put GROUP BY into preceding load. You can however use resident load instead.

Moreover all non-aggregated columns from SELECT list are required in your GROUP BY clause, so you should name fields explicitly instead of using star (*)

BTW, please note that "Sales" would be also included in selection list with start symbol used

Lukasz

Re: Preceding Load

maxgro — Mon, 10 Feb 2014 11:34:59 GMT

Lukasz,

mee too I didn't think but I tried and it works (group by in preceding load)

you are right on "Moreover all non-aggregated columns from SELECT list are required in your GROUP BY clause, so you should name fields explicitly instead of using star (*)"

So putting all togheter (group by, preceding and Lukasz's input) this works

LOAD

OrderID, ProductID,

//, Product, CustomerID, EmployeeID, OrderDate,

Margin, Cost, Quantity, Sales,

Sum(Sales) as TotalSales,

Sum(Sales-Margin)-1 as %Margin

GROUP BY OrderID, ProductID, //Product, CustomerID, EmployeeID, OrderDate,

Margin, Cost, Quantity, Sales

;

LOAD

OrderID,

ProductID,

Product,

CustomerID,

EmployeeID,

OrderDate,

Year(OrderDate) as Year,

'Q'&Ceil(Month(OrderDate)/3) as Quarter,

Month(OrderDate) as Month,

MonthName(OrderDate) as MonthYear,

Week(OrderDate) as Week,

Margin,

Cost,

Quantity,

Sales

FROM [Sales.xls] (biff, embedded labels, table is Orders$)

Re: Preceding Load

Mon, 10 Feb 2014 11:50:23 GMT

Thanks lmastalerz