Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
LordGrim
Contributor III
Contributor III

Group By and Sum on Excel import to load script

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

Labels (1)
1 Solution

Accepted Solutions
LordGrim
Contributor III
Contributor III
Author

Thanks that did the trick 🙂

 

View solution in original post

2 Replies
klikgevoel
Contributor III
Contributor III

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
Contributor III
Contributor III
Author

Thanks that did the trick 🙂