Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
fishing_weights
Creator
Creator

Grouping values while maintaining existing category in load script

Hi Qlik Experts

I would like to group and sum the values when maintaining the original values in a table in the load script.

Other then breaking them out and concatenating back to the same table would there be a better way?

Thanks in advance

 

Original Table:

CategoryValues
apple1
orange5
fish3
chicken7

 

Final Output:

CategoryValues
Fruits6
apple1
orange5
Animals10
fish3
chicken 7
2 Replies
priyanka181088
Partner - Contributor II
Partner - Contributor II

apologies for the syntax error as written it without load script.

 

Map_group:
mapping Load * inline [
Category, Class
apple, fruits
orange, fruits
fish, animals
chicken, animals];

Table1:
load Category,
Value,
Applymap('Map_group',Category,<unknown>) as Class
Resident Table;

 

Noconcatenate

Table2:

Load
Class as Category,
sum(Value) as value

Resident Table1
group by Class;

Concatenate

load Category,
Value
Resident Table

drop table Table, Table1;

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I think you can modify your script a bit to avoid the temp tables:

Table1:
Load * Inline [
Category, Values
apple, 1
orange, 5
fish, 3
chicken, 7
]
;
 
Map_group:
mapping Load * inline [
Category, Class
apple, fruits
orange, fruits
fish, animals
chicken, animals];
 
Concatenate (Table1)
Load 
  Sum(Values) as Values,
  ApplyMap('Map_group', Category, 'unknown') as Category
Resident Table1
Group By ApplyMap('Map_group', Category, 'unknown')
;

 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com