Splitting column values with wildmatch condition.

[#1]

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

[stigchel]

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 ';');

[sunny_talwar]

Try this:

Table:

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

LOAD * Inline [

Country

US,MX,CH

CH

MX,BR,IND

SA,NA,US

] (delimiter is |);

[#4]

Thank you for your reply.

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

[sunny_talwar]

‌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

[chaper]

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;

[#7]

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.

[#8]

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.

[sunny_talwar]

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 |);

[#10]

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.