text transformation

[lionel_t]

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

[its_anandrjs]

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

];

Regards

Anand

[alexandros17]

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

where mytable is your source table

[datanibbler]

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]

Hello,

     it works thank you all.

Lionel TEA

[Anonymous]

Hi

Could you please mark question as answered.

Thank you.

[its_anandrjs]

Hi Lionel,

Close the thread by appropriate answers.

Regards

Anand