Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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 |);

13 Replies
stigchel
Honored Contributor

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.

Community Browser