if function??

[ranibosch]

hi,

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

I have the following field with respective values in it:

BU

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

[Or · Accepted solution]

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)

[antoniotiman]

Re: New field creation using existing fields

Re: New field creation using existing fields

[Anonymous]

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

[

BU

MONT

OPS

SHOP

NPD

];

[Anonymous]

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]

[MarcoWedel]

Hi,

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

Mapping ‒ QlikView

Mapping functions ‒ QlikView

hope this helps

regards

Marco

[el_aprendiz111]

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