Grouping of Column values

[#1]

I have a column called States.

STATES

AL

GA

MI

SFO,SAN,WD,MI

MD;SAN

FL

Note:I have , and ; and space within the values.SFO,SAN,WD,MI has ',' in between where as MD;SAN has ';' in between and also

before GA there is a space.

How can I group this column values as below.SFO and SAN should fall under CAL.So,here new row called CAL gets created for SFO and SAN.

STATES

AL

GA

MI

WD

MD

FL

CAL

[lironbaram]

hi

per your example

you can use this script

/////////////replacing value for sfo and san////////////////

load if(match(STATES,'SFO','SAN')>0,'CAL',STATES) AS STATES;

/////////////creating one value from raw data/////////////

load subfield(replace(trim(STATES),';',','),',') AS STATES;

LOAD * INLINE [

    STATES

    AL

    GA

    MI

    "SFO,SAN,WD,MI"

    MD;SAN

    FL

];

[#3]

Thank you.Here I am losing all  other dimensions once I reload the script.STATES is displaying exactly how I want.

load if(match(STATES,'SFO','SAN')>0,'CAL',STATES)  AS STATES;

load subfield(replace(trim(STATES),';',','),',') AS STATES;

Can I combine these 2 script something like below ?

load if(match(STATES,'SFO','SAN')>0,'CAL',STATES)   and subfield(replace(trim(STATES),';',','),',') AS STATES;

[sasiparupudi1]

try

map11:

Mapping load * Inline

[

from ,to

SFO,CAL

SAN,CAL

];

aaaa:

load  Distinct subfield(MapSubString('map11',replace(trim(STATES),';',',')),',') AS STATES;

LOAD * INLINE [

    STATES

    AL

    GA

    MI

    "SFO,SAN,WD,MI"

    MD;SAN

    FL

];

[#5]

Same thing here too.This is removing all other dimensions.Only I see is STATES dimension.

[sasiparupudi1]

Can you pl post a sample

[#7]

Hi

I couldnt upload the file here,so I created another thread Called grouping filed values.Can you please look into it ?

Thank you very much.