topic New field with if logic help in QlikView

New field with if logic help

mahitham — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me to derive [Code Level ] field based on below conditions

If NAME field matches an entry in the Code A then [Code Level ] is 'A' else

If NAME field matches an entry in the Code B then [Code Level ] is 'B' else

If NAME field matches an entry in the Code C then [Code Level ] is 'C' else Code Level ] is 'D'

Here NAME field is coming from Table 1 and Code A , Code B , Code C fields coming from Table 2. From these 4 fields need to derive Code Level column.

Table 1 with data like below

Key NAME Amount

16/07/2018| FRIGJ-A1 FRIGJ 0.56893

16/07/2018| FRIGJ-A2 FRIGJ 0.16893

17/07/2018| FRIGJ-A1 FRIGJ 8.26893

17/07/2018| FRIGJ-A2 FRIGJ 9.26893

16/07/2018|IKRLP-A1 IKRLP 2.59688

16/07/2018|IKRLP-A2 IKRLP 2.86592

17/07/2018|IKRLP-A1 IKRLP 2.59688

17/07/2018|IKRLP-A2 IKRLP 28.5968

16/07/2018|OKLP-A1 OKLP 2.59688

16/07/2018|OKLP-A2 OKLP 2.86592

17/07/2018|OKLP-A1 OKLP 2.59688

17/07/2018|OKLP-A2 OKLP 28.5968

and Table 2 with data like below

Network Code A Code B Code C

A FRIGJ HUKO LKIOG

B 86938 IKRLP

C OKLIP OKLI

Table1:

Load

Key ,

NAME ,

Amount

From …..

Table2:

Load

Network,

Code A ,

Code B,

Code C

From …..

Please help me on this.

Thanks in advance.

Re: New field with if logic help

sunny_talwar — Wed, 18 Jul 2018 10:14:45 GMT

Try this

Table2:

LOAD * INLINE [

Network, Code A, Code B, Code C

A, FRIGJ, HUKO, LKIOG

B, , 86938, IKRLP

C, , OKLIP, OKLI

];

Table:

LOAD *,

If(Exists([Code A], NAME), 'A',

If(Exists([Code B], NAME), 'B',

If(Exists([Code C], NAME), 'C', 'D'))) as [Code Level];

LOAD * INLINE [

Key, NAME, Amount

16/07/2018| FRIGJ-A1, FRIGJ, 0.56893

16/07/2018| FRIGJ-A2, FRIGJ, 0.16893

17/07/2018| FRIGJ-A1, FRIGJ, 8.26893

17/07/2018| FRIGJ-A2, FRIGJ, 9.26893

16/07/2018|IKRLP-A1, IKRLP, 2.59688

16/07/2018|IKRLP-A2, IKRLP, 2.86592

17/07/2018|IKRLP-A1, IKRLP, 2.59688

17/07/2018|IKRLP-A2, IKRLP, 28.5968

16/07/2018|OKLP-A1, OKLP, 2.59688

16/07/2018|OKLP-A2, OKLP, 2.86592

17/07/2018|OKLP-A1, OKLP, 2.59688

17/07/2018|OKLP-A2, OKLP, 28.5968

];

Re: New field with if logic help

JaMajka1 — Wed, 18 Jul 2018 10:33:22 GMT

Hi Mahitha,

another possibility is to use applymap() with default value. If the value in field NAME exists in map M_code then code_level will be the result. If the value is not here, 'D' is set as a default value. This solution is more general because it does not depend on specific count of code levels.

[M_code]:

mapping load * Inline

[code,code_level

FRIGJ,A

HUKO,B

86938,B

OKLIP,B

LKIOG,C

IKRLP,C

OKLI,C

];

Table:

LOAD *,

applymap('M_code',NAME,'D') as [Code level new];

LOAD * INLINE [

Key, NAME, Amount

16/07/2018| FRIGJ-A1, FRIGJ, 0.56893

16/07/2018| FRIGJ-A2, FRIGJ, 0.16893

17/07/2018| FRIGJ-A1, FRIGJ, 8.26893

17/07/2018| FRIGJ-A2, FRIGJ, 9.26893

16/07/2018|IKRLP-A1, IKRLP, 2.59688

16/07/2018|IKRLP-A2, IKRLP, 2.86592

17/07/2018|IKRLP-A1, IKRLP, 2.59688

17/07/2018|IKRLP-A2, IKRLP, 28.5968

16/07/2018|OKLP-A1, OKLP, 2.5968

16/07/2018|OKLP-A2, OKLP, 2.86592

17/07/2018|OKLP-A1, OKLP, 2.59688

17/07/2018|OKLP-A2, OKLP, 28.5968

];

Hope it helps ,

Maria