topic Re: if function?? in QlikView

if function??

ranibosch — Thu, 02 Nov 2017 14:05:00 GMT

hi,

how do i write in script for example the following scenario.

I have the following field with respective values in it:

MONT

OPS

SHOP

NPD

I want to create a 2nd field called Responsibility Center that should populate as follows.

if BU = MONT; the responsibility center = MT

if BU = OPS or SHOP; the responsibility center = WR

if BU = NPD; responsibility center = NPD

So the table will then look as follows:

BU RESPONSIBILITY CENTER

MONT MT

OPS WR

SHOP WR

NPD NPD

Re: if function??

Or — Thu, 02 Nov 2017 14:09:05 GMT

Seems like a straightforward nested if():

If(BU = 'MONT','MT',if(BU = 'NPD','NPD','WR') as RESPONSIBILITY_CENTER

(Note that I used the ELSE section for the WR combination, but you can write it out in full if you prefer)

Re: if function??

antoniotiman — Thu, 02 Nov 2017 14:14:02 GMT

Re: New field creation using existing fields

Re: if function??

Anonymous — Thu, 02 Nov 2017 14:16:48 GMT

Do a resident load into the table with a nested IF:

LOAD *,

if(BU='MONT','MT',if(BU='OPS' OR BU='SHOP','WR',if(BU='NPD','NPD',0))) AS [Responsibility Center];

LOAD * Inline

[

MONT

OPS

SHOP

NPD

];

Re: if function??

Anonymous — Thu, 02 Nov 2017 14:33:45 GMT

Instead of the IF statement you could also use a Pick and nested Match:

Pick(Match(BU,'MONT','OPS','SHOP','NPD'),'MT','WR','WR','NPD') AS [Responsibility Center]

Re: if function??

MarcoWedel — Fri, 03 Nov 2017 22:33:42 GMT

Hi,

another solution could be a mapping table and the ApplyMap() function:

Mapping ‒ QlikView

Mapping functions ‒ QlikView

hope this helps

regards

Marco

Re: if function??

el_aprendiz111 — Fri, 03 Nov 2017 22:55:51 GMT

Hi Rani

Mapping
tmp:
LOAD * Inline
[
BU,RESPONSIBILITY CENTER
MONT,MT
OPS,WR
SHOP,WR
NPD,NPD
];

MyTbl:
LOAD *, ApplyMap('tmp',BU,'N/A') AS RES_CENTER;
LOAD * Inline
[
BU
MONT
OPS
SHOP
NPD
];