How can I turn one field of data into multiple fields?

[Anonymous]

Hi!

I Have the following data in a field

COD:

FDMX
FDSP
FRMP
FRRE
FRWE
IMMX
IMPA
ITAT
ITMA
ITMP
ITPS
ITRE
ITVP
ITWE
ITWF
MPMX
MXPS
MXWE
MXWF
PRCA
PSFR
PSOL
REDS
REFR
REIT
REUK

In this field there is identified the country and another Identifier (Example: REFR,FRRE = France). So I would like to create a Field that contains the country and this is linked to the COD field. If we follow the previous example, when we select FR the field that should be selected from COD are REFR and FRRE.

Any idea of how to do this without doing manually?

Thanks!

[llauses243]

hI,

this is my offer ...

//script:

Result:

Load rigth(COD, 2) as country, COD Resident COD;

Load left(COD, 2) as country, COD Resident COD;

Drop table COD;

//end script

good luck

/Luis.

[#3]

Hello,

Tab1:
LOAD
COD,
<other>
<from whatever source>;

I just wonder, how any algorithm can decide, whether to take the first two, or the last two letters.
So just for the basics, I assume only the right 2 letters are relevant:

Tab2:
LOAD Distinct
right(COD,2) as Country,
COD
resident Tab1;

To solve the left/right issue, I would say you need some kind of flag to determine, whether to take left or right.
Let's hope there is any other field within your data which gives you the information, then it would look similar to

Tab2:
LOAD Distinct
if(MyFlagField='MyFlagInformationSaysLeft', left(COD,2), right(COD,2) as Country,
COD
Resident Tab1;

hth,
Thilo