How to create a calculated column if condition with multiple values from a column in different table

[swetha54]

Hi All,

We are trying to create a new column in a table1, called Director where Level 6 column has values in the list of Directors column in other table2. 

Can anyone please suggest on this.

[asinha1991]

can you share an example? 

[Rohan]

Hello, Try ApplyMap() function. Firstly load the list of those Directors who satisfy your condition like : [Director's Map] : mapping Load Primary Key, //Unique field between the two tables [Directors] // List that you need in your second Table resident Table2 ; Table : //This is the Table where you need the list of other directors Load *, If ( Level of Director = 6, ApplyMap('Director's Map',Primary Key,null()),null()) as List_of_Second_Directors From (...) Try this & Let me know if you have any Queries. Thanks & Regards Rohan S. Desai

[swetha54]

Hi,

New Column to be created by comparing  Level 6 and Level 5 columns with Director column in Table2. Just as an example i have  taken 2 values in Table 2 but there are many.

if (Level 6 in Director from table 2, Level 6, if(level 5 in Director from table 2, level5, NA))

this if statement works in SQL, but how to write it in Qliksense.

Table 1
Emp	Level 5	Level 6	New Column
E01	ABC	NA	ABC
E02	NA	NA	NA
E03	NA	XYZ	XYZ

Table2

Director

ABC

XYZ

[swetha54]

Could you please tell me how it can be applied to my example

[asinha1991 · Accepted solution]

CHECKTABLE:

MAPPING Load DIRECTOR,Rowno() as rno RESIDENT CHECKTABLE from TABLE2;

TABLE1:
Load Emp, [Level 5],[ Level 6],
if(not applymap('CHECKTABLE',[Level 5],'NA')='NA',[Level 5],
if(not applymap('CHECKTABLE',[Level 6],'NA')='NA',[Level 6],'NA')) as Director
from table 1;

[Rohan]

Hello, 

You don't even need any Mapping. All you need a simple nested If statement like :

If(

       Isnull(level6)=0,

                             level6,

                             If(

                                  Isnull(level5)=0,

                                         level5,

                                              'N/A'

                                   )

) as [New Column]

In this New Column you should get your desired results. 

Let me know if this worked for you or if you have any queries.

Thank & Regards 

Rohan S. Desai

[swetha54]

Thanks Asinha!

it is working fine for one column.

Can you please help for creating one more new column called Associate Director and in check table there is another column coaled Associate director.