topic Re: How to group row values in QlikView

How to group row values

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the following scenario:

These are the values in a field from a table:

Red

Blue

Amber

Green

Blue

Amber

I would like to group Red and Blue into a new value called Primary and group Amber and Green into a new value called Secondary.

These are not columns but rows in a column, if that makes sense,

Thanks in advance!

Re: How to group row values

sunny_talwar — Wed, 30 Aug 2017 13:31:24 GMT

May be like this

In script:

If(Match(FieldName, 'Red', 'Blue'), 'Primary', 'Secondary') as NewFieldName

As a calculated dimension

If(Match(FieldName, 'Red', 'Blue'), 'Primary', 'Secondary')

Re: How to group row values

Anil_Babu_Samineni — Wed, 30 Aug 2017 13:31:44 GMT

May be this?

If(Match(Final,'Red','Blue'), 'Primary','Secondary')

Re: How to group row values

Wed, 30 Aug 2017 13:37:38 GMT

Will that not create a new field though?

In the script I have:

Colour Load:

LOAD Colours,

[Colours Group]/*,

FROM

[.\User Permissions.xls]

(biff, embedded labels, table is Sheet1$);

The rows are contained within the Colour field.

Thanks!

Re: How to group row values

its_anandrjs — Wed, 30 Aug 2017 13:40:36 GMT

Try is this work for you

MapTable:

Mapping LOAD * Inline

[

MappingName,NewGrp

Red,Primary

Blue,Primary

Amber,Secondary

Green,Secondary

Blue,None

Amber,Secondary

];

MainTable:

LOAD *,ApplyMap('MapTable',Names,'None') as NewGroup;

LOAD * Inline

[

Names

Red

Blue

Amber

Green

Blue

Amber

];

Re: How to group row values

Anil_Babu_Samineni — Wed, 30 Aug 2017 13:42:18 GMT

Yes, This is where Sunny and Myself did

[Colour Load]:

LOAD Colours, //If(Match(Colours,'Red','Blue'), 'Primary','Secondary') as Colours

[Colours Group]/*,

FROM

[.\User Permissions.xls]

(biff, embedded labels, table is Sheet1$);

Re: How to group row values

its_anandrjs — Wed, 30 Aug 2017 13:42:39 GMT

You have to create another calculated dimension.

Re: How to group row values

sunny_talwar — Wed, 30 Aug 2017 13:42:59 GMT

It will, isn't that what you want?

Color:

LOAD Colours

If(Match(Colours, 'Red', 'Blue'), 'Primary', 'Secondary') as [Colours Group]

FROM

[.\User Permissions.xls]

(biff, embedded labels, table is Sheet1$);

Re: How to group row values

its_anandrjs — Wed, 30 Aug 2017 13:48:44 GMT

Please elaborate more your question or you can try this.

MapTable:

Mapping LOAD * Inline

[

MappingName,ColoursNewGrp

Red,Primary

Blue,Primary

Amber,Secondary

Green,Secondary

Blue,Primary

Amber,Secondary

];

MainTable:

LOAD *,ApplyMap('MapTable',Colours,'None') as ColoursGrp;

LOAD * Inline

[

Colours

Red

Blue

Amber

Green

Blue

Amber

];

Re: How to group row values

Wed, 30 Aug 2017 13:50:09 GMT

Thank you!