topic Re: Grouping of Column values in QlikView

Grouping of Column values

Tue, 01 Sep 2015 13:21:11 GMT

I have a column called States.

STATES

SFO,SAN,WD,MI

MD;SAN

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

CAL

Re: Grouping of Column values

lironbaram — Tue, 01 Sep 2015 13:36:12 GMT

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

"SFO,SAN,WD,MI"

MD;SAN

];

Re: Grouping of Column values

Tue, 01 Sep 2015 13:55:51 GMT

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;

Re: Grouping of Column values

sasiparupudi1 — Tue, 01 Sep 2015 14:21:46 GMT

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

"SFO,SAN,WD,MI"

MD;SAN

];

Re: Grouping of Column values

Tue, 01 Sep 2015 14:28:57 GMT

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

Re: Grouping of Column values

sasiparupudi1 — Tue, 01 Sep 2015 14:41:01 GMT

Can you pl post a sample

Re: Grouping of Column values

Tue, 01 Sep 2015 15:24:58 GMT

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.