Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danyal_akhlaq
Contributor III
Contributor III

How to compare a column with different columns of another table and generate a new column?

I am loading two tables from two different excel files, but on load i have to map the Designation in one column and i have tried IF condition but it is not working fine.


Please suggest.

TABLE-1

DIRECTORY;

EmployeeAttendance:

LOAD

    EmployeeCode,

    Date,

    Time,

    Location,

    Status

FROM

EmployeeAttendance.xlsx

(ooxml, embedded labels, table is EmployeeAttendance);


TABLE-2


DIRECTORY;

EmployeeHierarchy:

LOAD

    ZoneManagerName,

    ZoneManagerCode,

    AreaManagerName,

    AreaManagerCode,

    FieldManagerName,

    FieldManagerCode,        IF(EmployeeCode=ZoneManagerCode,'ZM',IF(EmployeeCode=AreaManagerCode,'AM',IF(EmployeeCode=FieldManagerCode,'FM','OTHER'))) as Designation,

FROM

EmployeeHierarchy.xlsx

(ooxml, embedded labels, table is EmployeeHierarchy);

3 Replies
danyal_akhlaq
Contributor III
Contributor III
Author

Sorry, i forgot to include the link between tables, means that the column EmployeeCode is in both tables.

TABLE-1

DIRECTORY;

EmployeeAttendance:

LOAD

    EmployeeCode,

    Date,

    Time,

    Location,

    Status

FROM

EmployeeAttendance.xlsx

(ooxml, embedded labels, table is EmployeeAttendance);


TABLE-2


DIRECTORY;

EmployeeHierarchy:

LOAD

    ZoneManagerName,

    ZoneManagerCode,

    AreaManagerName,

    AreaManagerCode,

    FieldManagerName,

    FieldManagerCode,

    EmployeeCode,

IF(EmployeeCode=ZoneManagerCode,'ZM',IF(EmployeeCode=AreaManagerCode,'AM',IF(EmployeeCode=FieldManagerCode,'FM','OTHER'))) as Designation,

FROM

EmployeeHierarchy.xlsx

(ooxml, embedded labels, table is EmployeeHierarchy);

sujan24s
Contributor III
Contributor III

Hi,

        You can write

   Pick(Match(EmployeeCode,'ZoneManagerName',' ZoneManagerCode',,AreaManagerCode','FieldManagerName','FieldManagerCode' ),'ZM','AM','FM','FMC')As Designation

danyal_akhlaq
Contributor III
Contributor III
Author

TABLE-1

EmployeeCodeDateTimeLocationStatus
E000122/6/201809:00:00KHIPresent
E000322/6/201809:00:00KHIPresent
E000622/6/201809:00:00KHIAbsent

TABLE-2

ZoneManagerNameZoneManagerCodeAreaManagerNameAreaManagerCodeFieldManagerNameFieldManagerCodeEmployeeCode
AliE0001FarrukhE0003SaadE0006E0006
AliE0001FarrukhE0003FahadE0007E0007
AliE0001RafiE0004AhadE0008E0008
AsimE0002QasimE0005FarhanE0009E0009
AsimE0002QasimE0005OvaisE0010E0010
AsimE0002QasimE0005RashidE0011E0011

RESULT TABLE


Designation column can be populated by Matching EmployeeCode (Table-1) with ZoneManagerCode, AreaManagerCode, FieldManagerCode (Table-2)



oneManagerNameZoneManagerCodeAreaManagerNameAreaManagerCodeFieldManagerNameFieldManagerCodeEmployeeCodeDesignation
AliE0001FarrukhE0003SaadE0006E0006
AliE0001FarrukhE0003FahadE0007E0007
AliE0001RafiE0004AhadE0008E0008
AsimE0002QasimE0005FarhanE0009E0009
AsimE0002QasimE0005OvaisE0010E0010
AsimE0002QasimE0005RashidE0011E0011