topic Table in QlikView

Table

smilingjohn — Thu, 25 Feb 2016 12:18:56 GMT

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

Re: Table

Colin-Albert — Thu, 25 Feb 2016 12:25:51 GMT

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);

Re: Table

Anonymous — Thu, 25 Feb 2016 12:26:23 GMT

like this?

Re: Table

Anonymous — Thu, 25 Feb 2016 12:29:20 GMT

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

];

Re: Table

Colin-Albert — Thu, 25 Feb 2016 12:29:53 GMT

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

Re: Table

smilingjohn — Thu, 25 Feb 2016 12:35:37 GMT

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

Re: Table

Anonymous — Thu, 25 Feb 2016 12:40:04 GMT

like this?

Reverse string

Re: Table

Colin-Albert — Thu, 25 Feb 2016 12:49:11 GMT

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?