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: 
Not applicable

If Exists

I have a scenario in which i need to check value from the table exists in the excel file and assign a flag.

I have a table of employee containing employeeid and department, there is another table containing employee education info (employeeid and degree).

Now i have an excel list containing department and degree as two separate columns.

I need to check if the department of employee AND degree of the employee EXISTS in the list , put a flag(1) on it else 0 in employee table.

7 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

I don't quite follow what you're asking. Can you provide an example?

Not applicable
Author

Table 1:Employee

EmpId     Name     Department

1               Abc          IT

2               XYZ          IT

3               PQR          Finance

Table 2: Employee_Education

EmpId     Degree

1               MS(IT)

2               BS(IT)

3               BCom

Excel List:

Department     Degree

IT                         MS(IT)

IT                         BS(IT)

REQUIRED TABLE

EmpId     Name     Department     Flag

1               Abc         It                    True

2               XYZ          IT                 True

3               PQR        Finance         False

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Thanks for the example. Try this:

Map_DeptDeg:

MAPPING LOAD

  Department & '/' & Degree

  True

FROM Excel...;

Employees:

LOAD

   EmpID

   ,Name

   ,Department

FROM Table1...;

LEFT JOIN (Employees)

LOAD

  EmpID

  ,Degree

FROM Table2...;

Final:

LOAD

*

,ApplyMap('Map_DeptDeg',Department & '/' & Degree,'False') AS Flag

RESIDENT Employees;

DROP TABLE Employees;

Hope this helps,

Jason

Not applicable
Author

Thanks  jasons for your reply.

The solution is not working for me, it is giving all False except 2 employees which dont have any department.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

There's a typo in my Mapping script:

 

Map_DeptDeg:

MAPPING LOAD

  Department & '/' & Degree

  ,True

FROM Excel...;

If this still doesn't work please post some sample data or better still your app and I'll take a look.

Jason

Not applicable
Author

Thanks Jason

I have used the code already removed the typo, but the problem is still there, to make it more clear in my excel list ; department column contain 5 entries whereas the degree contains around ten entries,

do u think it might creating the error??

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Can you provide some sample data?