topic Re: Splitting column values with wildmatch condition. in QlikView

Splitting column values with wildmatch condition.

Mon, 31 Aug 2015 15:01:28 GMT

I have a column called Country with values as below

Country

US,MX,CH

MX,BR,IND,

SA,NA,US

Using wild match How can i make these column values as individual columns like US,MX,CH,BR,IND,SA,NA

Re: Splitting column values with wildmatch condition.

stigchel — Mon, 31 Aug 2015 15:14:36 GMT

You can use SubField to create a row for each comma seperated value like this

Data:

Load SubField(Country,',') as Country;

Load * Inline [Country

US,MX,CH

MX,BR,IND

SA,NA,US

](delimiter is ';');

Re: Splitting column values with wildmatch condition.

sunny_talwar — Mon, 31 Aug 2015 15:17:01 GMT

Try this:

Table:

LOAD SubField(Country, ',') as Country;

LOAD * Inline [

Country

US,MX,CH

MX,BR,IND

SA,NA,US

] (delimiter is |);

Re: Splitting column values with wildmatch condition.

Mon, 31 Aug 2015 15:20:05 GMT

Thank you for your reply.

I dont want to do a inline load.Can i do it with the wildmatch within the script ?

Re: Splitting column values with wildmatch condition.

sunny_talwar — Mon, 31 Aug 2015 15:24:25 GMT

‌You don't need the inline load. That was just to show how it would work. You just need the preceding load on top of your table load with the SubField function to create multiple rows of data. If you want something else, please explain your expected output.

Best,

Sunny

Re: Splitting column values with wildmatch condition.

chaper — Mon, 31 Aug 2015 15:29:22 GMT

While loading the table use the SubField.Don't use the Subfield on intial load .use it in Resident load.I have similar requirement taken care with Subfield function.See the script below I used to get the list of values as individual values.

Script provided by sunindia‌ and Stigchel‌ should work.

Table:

Load Country,

City,

Street

From Location;

Load Country,

Subfield(Country,',') as COUNTRY

Resident table;

Re: Splitting column values with wildmatch condition.

Mon, 31 Aug 2015 15:53:07 GMT

Ya,that did work but I firgot to mention that column value has ';' too ex.SA;NA;US .

Country

US,MX,CH

MX,BR,IND,

SA;NA;US.

Thank you again.

Re: Splitting column values with wildmatch condition.

Mon, 31 Aug 2015 15:58:34 GMT

Ya,that did work but I forgot to mention that column value has ';' too ex.SA;NA;US .

Country

US,MX,CH

MX,BR,IND,

SA;NA;US.

Thank you again.

Re: Splitting column values with wildmatch condition.

sunny_talwar — Mon, 31 Aug 2015 15:58:51 GMT

Try this in that case:

Table:

LOAD SubField(SubField(Country, ','), ';') as Country;

LOAD * Inline [

Country

US,MX,CH

MX,BR,IND

SA;NA;US

] (delimiter is |);

Re: Splitting column values with wildmatch condition.

Mon, 31 Aug 2015 16:08:00 GMT

Thank you.That worked as well,now I see space in front of the column values.Ex:CH and US.I am so sorry.I should have mentioned it earlier.Thank you for your patience.

Country

US,MX,CH

MX,BR,IND,

SA;NA;US.

Re: Splitting column values with wildmatch condition.

Mon, 31 Aug 2015 16:10:09 GMT

I tried this.

subfield(subfield(Subfield([Country],','),';'),' ') as [Country]

Re: Splitting column values with wildmatch condition.

sunny_talwar — Mon, 31 Aug 2015 16:12:50 GMT

Not sure what you mean, but may be this:

Table:

LOAD Trim(SubField(SubField(Country, ','), ';')) as Country;

LOAD * Inline [

Country

US,MX,CH

MX,BR,IND

SA;NA;US

] (delimiter is |);

Re: Splitting column values with wildmatch condition.

Mon, 31 Aug 2015 16:45:59 GMT

thank you very much.

Re: Splitting column values with wildmatch condition.

Tue, 01 Sep 2015 13:35:20 GMT

Is the below doable ?Thank you.

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