Group By and Sum on Excel import to load script

[LordGrim]

I want to add a Group By for 'Part_Number' and Sum for 'Sale'. Happy to do with SQL but on an Excel import that option does not appear. Can someone show me how and where to apply that clause in the script below? I do not want to do front end as a measure.

Thankyou
---------------------------------------------

LOAD

Part_Number,
STATUS,
Sale

FROM [lib://SALES/Sales_Log.xlsx]
(ooxml, embedded labels, table is SalesExportsLog) WHERE STATUS Like 'Confirmed';

[klikgevoel]

In Qlik, and also in other languages, you'll have to provide the variables in the group by clause you want to group to.

Keep the initial load.

[TmpTable]:
LOAD
Part_Number,
STATUS,
Sale
FROM [lib://SALES/Sales_Log.xlsx]
(ooxml, embedded labels, table is SalesExportsLog)
Where STATUS Like 'Confirmed';

[GroupedTable]:
LOAD
Part_Number,
STATUS,
Sum(Sale) as TotalSale
Resident TmpTable
Group By Part_Number,STATUS,Sale;

Drop Table TmpTable;

Required for group by is an aggregation function like Sum()

[LordGrim · Accepted solution]

Thanks that did the trick 🙂