topic Re: If Exists in QlikView

If Exists

Tue, 31 Jul 2012 04:57:52 GMT

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.

Re: If Exists

Jason_Michaelides — Tue, 31 Jul 2012 06:15:02 GMT

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

Re: If Exists

Tue, 31 Jul 2012 06:31:50 GMT

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

Re: If Exists

Jason_Michaelides — Tue, 31 Jul 2012 06:40:32 GMT

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

Re: If Exists

Tue, 31 Jul 2012 09:13:58 GMT

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.

Re: If Exists

Jason_Michaelides — Tue, 31 Jul 2012 17:33:38 GMT

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

Re: If Exists

Wed, 01 Aug 2012 03:41:52 GMT

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??

Re: If Exists

Jason_Michaelides — Thu, 02 Aug 2012 09:00:28 GMT

Can you provide some sample data?