Table

[smilingjohn]

Hi ALl

At the script level  I  want to split name in to two columns first name and last name  or i can reverse the name itself ? and also under the field Petent i want to have one more field where upon DL needs to be replaced by DELL

and RR needs to be replaced by RARE.

PLease find the attachment

Regards

[Colin-Albert · Accepted solution]

Try this

LOAD Petent,

          replace(replace(Petent, 'DL', 'DELL'), 'RR', 'RARE') as PetentFix,
     Name,

      subfield(Name, '  ', 1) as firstname,

     subfield(Name, '  ', -1) as lastname

FROM

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

like this?

[Anonymous]

For 2nd part, try with replace() as suggested by Colin Albert

If you are having list of values..then create a inline in script and link it...like?

Load * inline

[Petent, Desc

DL, Dell

RR, Rare

CC, CabinClass

FC, FirstClass

];

[Colin-Albert]

Using subfield with -1 for lastname will work if the name has a middle name.

[smilingjohn]

Thanks Colin,

Its working

Is ther any way to reverse the name  for example Under Field Name it is =

Wright John

And the out put should be like John Wright

[Anonymous]

like this?

Reverse string

[Colin-Albert]

Not easy to swap the order unless all data is surname first.

The Reverse String solution suggested by balrajahlawat‌ would change "Wright John" to "nhoJ thgirW"

You would need to flag which names are in the wrong order in your source data with an additional field. Then use a conditional expression to identify whether you use +1 or -1 in the subfield command.

Some names are indeterminate    for example  Meredith John or John Meredith - which one is surname first?