topic Re: replace column if exists in QlikView

replace column if exists

Fri, 27 Jun 2014 09:05:59 GMT

hi,

i had a question

i had a table like that:

Client2013 Client2014 Client2015

QUICK - Mcdo

Flunch KFC QUICK

Eli - -

Elo Youk -

Mcdo - QUICK

i'd like to have a new column with conditions if there is somthing in client 2014 replace in client2013 and if exists client2015 replace client2014(with replace client2013 if exist)

to resume, the result should be:

NvClients

Mcdo

QUICK

Eli

Youk

QUICK

how can i do? THANKS a lot and sorry for my bad english

Re: replace column if exists

Gysbert_Wassenaar — Fri, 27 Jun 2014 09:13:29 GMT

load

Client2013,

Client2014,

Client2015,

if(len(trim(Client2015)),Client2015,

if(len(trim(Client2014)),Client2014,Client2013)) as NvClients

from ...

Re: replace column if exists

Fri, 27 Jun 2014 09:32:46 GMT

thanks a lot but just a problem,

When Client2014 and Client 2015 not exists (Client2013 always exists) the result is "-" but i want Client2013

Exemple:

Client2013 Client2014 Client2015

Eli - -

With your script that give me "-" but i want "Eli" for the rest this is perfect! thanks

Re: replace column if exists

Gysbert_Wassenaar — Fri, 27 Jun 2014 09:34:57 GMT

Try:

load

Client2013,

Client2014,

Client2015,

if(len(trim(Client2015)) and Client2015 <> '-',Client2015,

if(len(trim(Client2014)) and Client2014 <> '-',Client2014,Client2013)) as NvClients

from...

Re: replace column if exists

Fri, 27 Jun 2014 09:42:14 GMT

Okay i understand the problem

when the name is too long because i had 1000 names, thats not put it in nvclient, may be beccause of len?

Re: replace column if exists

Gysbert_Wassenaar — Fri, 27 Jun 2014 09:45:40 GMT

Could be. You can try the IsNull function instead of len(trim(...)).

Re: replace column if exists

Fri, 27 Jun 2014 09:46:23 GMT

OKAY i gat the solution, i delete "len" and its ok! thank you very much!!!

Re: replace column if exists

Fri, 27 Jun 2014 09:49:31 GMT

Arf sorry but it's not ok xD

Re: replace column if exists

Fri, 27 Jun 2014 09:51:52 GMT

LOAD [Client2013 ],

Client2014,

Client2015,

if(len(trim(Client2015))=0,Client2014,if(len(trim(Client2014)=0),[Client2013 ])) as NvClients

use this may help you.

Re: replace column if exists

sasikanth — Fri, 27 Jun 2014 09:53:43 GMT

LOAD * Inline [
Client2013   , Client2014 ,   Client2015
QUICK    ,           -       ,     Mcdo
Flunch    ,          KFC       ,   QUICK
Eli      ,              -       , -
Elo      ,              Youk     ,    -
Mcdo     ,               -        , QUICK
];

LOAD
if(Client2015<>'-',Client2015,
if( Client2015='-',if( Client2014='-',Client2013,Client2014),Client2014)) as nta
Resident tab;

Re: replace column if exists

Fri, 27 Jun 2014 09:55:21 GMT

Sorry but error in expression : Len takes 1 parameter..

Re: replace column if exists

Fri, 27 Jun 2014 10:14:10 GMT

Sorry but i have an other problem..

That's ok when in the fields Client2013 there is a number in the field.

But when there is only letters "quick" for example that not work

Client2013 Client2014 Client2015 nvclient

QUICK - Mcdo -

Flunch22 - - Flunch22

thanks