Preceding Load

[#1]

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,

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 (biff, embedded labels, table is Orders$)

[#2]

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

[maxgro · Accepted solution]

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$)

[#4]

Thanks lmastalerz