Grouping values while maintaining existing category in load script

[fishing_weights]

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:

Category	Values
apple	1
orange	5
fish	3
chicken	7

Final Output:

Category	Values
Fruits	6
apple	1
orange	5
Animals	10
fish	3
chicken	7

[priyanka181088]

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 · Accepted solution]

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