topic Merge multiple rows into one in Qlikview in QlikView

Merge multiple rows into one in Qlikview

TosBlAk — Mon, 14 Feb 2022 18:22:28 GMT

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.

Re: Merge multiple rows into one in Qlikview

maxgro — Mon, 14 Feb 2022 20:23:01 GMT

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;