Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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_michaelid
Honored Contributor II

Re: If Exists

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

Not applicable

Re: If Exists

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_michaelid
Honored Contributor II

Re: If Exists

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

Re: If Exists

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_michaelid
Honored Contributor II

Re: If Exists

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

Re: If Exists

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_michaelid
Honored Contributor II

Re: If Exists

Can you provide some sample data?