How to group row values

[#1]

Hi,

I have the following scenario:

These are the values in a field from a table:

Red

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!

[sunny_talwar]

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')

[Anil_Babu_Samineni]

May be this?

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

[#4]

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!

[its_anandrjs · Accepted solution]

Try is this work for you

MapTable:

Mapping LOAD * Inline

[

MappingName,NewGrp

Red,Primary

Red,Primary

Blue,Primary

Amber,Secondary

Green,Secondary

Blue,None

Amber,Secondary

];

MainTable:

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

LOAD * Inline

[

Names

Red

Red

Blue

Amber

Green

Blue

Amber

];

[Anil_Babu_Samineni]

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$);

[its_anandrjs]

You have to create another calculated dimension.

[sunny_talwar]

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$);

[its_anandrjs]

Please elaborate more your question or you can try this.

MapTable:

Mapping LOAD * Inline

[

MappingName,ColoursNewGrp

Red,Primary

Red,Primary

Blue,Primary

Amber,Secondary

Green,Secondary

Blue,Primary

Amber,Secondary

];

MainTable:

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

LOAD * Inline

[

Colours

Red

Red

Blue

Amber

Green

Blue

Amber

];

[#10]

Thank you!