Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

lionel_t
New Contributor

text transformation

Hello,

    I have a modeling question. I want to turn my data from this format:

country

town
country1a;b;c;d
country2e;f;g

to this one:

country

town
country1a
country1b
country1c
country1d
country2e
country2f
country2

g

thanks for your help

6 Replies

Re: text transformation

Hi,

Yes load your table with subfield function

A:

Load country,    SubField(town,';') as Town;

LOAD * Inline

[

country,    town

country1,    a;b;c;d

country2,    e;f;g

];

sub.png

Regards

Anand

Re: text transformation

load country, subfield(town';') resident mytable;

where mytable is your source table

datanibbler
Esteemed Contributor

Re: text transformation

Hi Lionel,

if the separator is always the same (between the letters  in the column >town<), then you can use the SUBFIELD() function .- you must parse the table record by record and use SUBFIELD() on each one in turn - that will turn each one into as many records as there are letters.

HTH

Best regards,

DataNibbler

lionel_t
New Contributor

Re: text transformation

Hello,

     it works thank you all.

Lionel TEA

morris70
Valued Contributor

Re: text transformation

Hi

Could you please mark question as answered.

Thank you.

Re: text transformation

Hi Lionel,

Close the thread by appropriate answers.

Regards

Anand

Community Browser