New field with if logic help

[mahitham]

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.

[sunny_talwar · Accepted solution]

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

];

[JaMajka1]

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