Merge multiple rows into one in Qlikview

[TosBlAk]

Hi all, 

I have a field that has some records I'll like to combine into one.

For example:

FieldName (Colors)            (Count) Quantity 

Light Yellow                                             4

Lemon Yellow                                         7

Chrome Yellow                                       2

Red                                                              15

Green                                                           8

I want Light Yellow, Lemon Yellow and Chrome Yellow  to be a single entry as "Yellow". End Result should be:

FieldName (Colors)       (Count) Quantity 

Yellow                                               13

Red                                                    15

Green                                                 8

How can I go about this please?

Thanks in advance. 

[maxgro · Accepted solution]

If you have a table X with Light, Lemon, ...... Yellow

X:
load * inline [
Colors , Quantity
Light Yellow , 4
Lemon Yellow , 7
Chrome Yellow , 2
Red , 15
Green , 8
];

you can group the rows when there is Yellow in the Colors field and sum the quantity

Y:
NOCONCATENATE LOAD
IF(WILDMATCH(Colors, '*yellow*'), 'Yellow', Colors) as Colors,
SUM(Quantity) AS Quantity
RESIDENT X
GROUP BY IF(WILDMATCH(Colors, '*yellow*'), 'Yellow', Colors);

DROP TABLE X;