topic Re: Group By and Sum on Excel import to load script in App Development

Group By and Sum on Excel import to load script

LordGrim — Sat, 16 Nov 2024 02:29:56 GMT

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

Re: Group By and Sum on Excel import to load script

klikgevoel — Thu, 07 May 2020 12:49:44 GMT

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

Re: Group By and Sum on Excel import to load script

LordGrim — Mon, 11 May 2020 06:57:20 GMT

Thanks that did the trick 🙂