topic Re: text transformation in Qlik Learning Discussions

text transformation

lionel_t — Mon, 24 Nov 2014 14:05:22 GMT

Hello,

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

country	town
country1	a;b;c;d
country2	e;f;g

to this one:

country	town
country1	a
country1	b
country1	c
country1	d
country2	e
country2	f
country2	g

thanks for your help

Re: text transformation

its_anandrjs — Mon, 24 Nov 2014 14:21:30 GMT

Hi,

Yes load your table with subfield function

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

LOAD * Inline

[

country, town

country1, a;b;c;d

country2, e;f;g

];

Regards

Anand

Re: text transformation

alexandros17 — Mon, 24 Nov 2014 14:22:24 GMT

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

where mytable is your source table

Re: text transformation

datanibbler — Mon, 24 Nov 2014 14:23:34 GMT

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

Re: text transformation

lionel_t — Mon, 24 Nov 2014 14:38:41 GMT

Hello,

it works thank you all.

Lionel TEA

Re: text transformation

Anonymous — Tue, 25 Nov 2014 08:21:28 GMT

Could you please mark question as answered.

Thank you.

Re: text transformation

its_anandrjs — Tue, 25 Nov 2014 08:26:13 GMT

Hi Lionel,

Close the thread by appropriate answers.

Regards

Anand