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

[danyal_akhlaq]

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

[danyal_akhlaq]

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]

Hi,

        You can write

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

[danyal_akhlaq]

TABLE-1

EmployeeCode	Date	Time	Location	Status
E0001	22/6/2018	09:00:00	KHI	Present
E0003	22/6/2018	09:00:00	KHI	Present
E0006	22/6/2018	09:00:00	KHI	Absent

TABLE-2

ZoneManagerName	ZoneManagerCode	AreaManagerName	AreaManagerCode	FieldManagerName	FieldManagerCode	EmployeeCode
Ali	E0001	Farrukh	E0003	Saad	E0006	E0006
Ali	E0001	Farrukh	E0003	Fahad	E0007	E0007
Ali	E0001	Rafi	E0004	Ahad	E0008	E0008
Asim	E0002	Qasim	E0005	Farhan	E0009	E0009
Asim	E0002	Qasim	E0005	Ovais	E0010	E0010
Asim	E0002	Qasim	E0005	Rashid	E0011	E0011

RESULT TABLE

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

oneManagerName	ZoneManagerCode	AreaManagerName	AreaManagerCode	FieldManagerName	FieldManagerCode	EmployeeCode	Designation
Ali	E0001	Farrukh	E0003	Saad	E0006	E0006
Ali	E0001	Farrukh	E0003	Fahad	E0007	E0007
Ali	E0001	Rafi	E0004	Ahad	E0008	E0008
Asim	E0002	Qasim	E0005	Farhan	E0009	E0009
Asim	E0002	Qasim	E0005	Ovais	E0010	E0010
Asim	E0002	Qasim	E0005	Rashid	E0011	E0011