Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

Splitting column values with wildmatch condition.

I have a column called Country with values as below

Country

US,MX,CH

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

1 Solution

Accepted Solutions

Re: Splitting column values with wildmatch condition.

Not sure what you mean, but may be this:

Table:

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

LOAD * Inline [

Country

US,MX,CH

CH

MX,BR,IND

SA;NA;US

] (delimiter is |);

View solution in original post

13 Replies
Partner
Partner

Re: Splitting column values with wildmatch condition.

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

CH

MX,BR,IND

SA,NA,US

](delimiter is ';');

Re: Splitting column values with wildmatch condition.

Try this:

Table:

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

LOAD * Inline [

Country

US,MX,CH

CH

MX,BR,IND

SA,NA,US

] (delimiter is |);

Not applicable

Re: Splitting column values with wildmatch condition.

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.

‌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

chaiperi
Contributor III

Re: Splitting column values with wildmatch condition.

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;

Not applicable

Re: Splitting column values with wildmatch condition.

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

Country

Country

US,MX,CH

CH

MX,BR,IND,

SA;NA;US.

Thank you again.

Not applicable

Re: Splitting column values with wildmatch condition.

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

Country

Country

US,MX,CH

CH

MX,BR,IND,

SA;NA;US.

Thank you again.

Re: Splitting column values with wildmatch condition.

Try this in that case:

Table:

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

LOAD * Inline [

Country

US,MX,CH

CH

MX,BR,IND

SA;NA;US

] (delimiter is |);

Not applicable

Re: Splitting column values with wildmatch condition.

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

CH

US

MX,BR,IND,

SA;NA;US.