topic Re: Compare Excel data in QlikView

Compare Excel data

Tue, 17 Jul 2012 13:30:21 GMT

HI All

I have two excel files with employee details.

All Employees

Resigned Employess

Load the two excels and display only the employes who are working

Please Help

Venkat

Tue, 17 Jul 2012 14:04:28 GMT

You can create a field on resigned excel called Flag_Resigned and set all to 1

Set the employee name the same on both excel and load them with a join

The idea:

TEMP_DATA

Load EMPLOYEE_TEMP, OTHER_COLS_TEMP

from all.xls ...

join

Load EMPLOYEE_TEMP, FLAG_RESIGNED_TEMP

from resigned.xls ..

FINAL_DATA:

LOAD

EMPLOYEE_TEMP as EMPLOYEE,

OTHER_COLS_TEMP as OTHER_COLS,

if( isNull(FLAG_RESIGNED_TEMP), 0, FLAG_RESIGNED_TEMP) as FLAG_RESIGNED

resident TEMP_DATA;

drop table TEMP_DATA;

nagaiank — Tue, 17 Jul 2012 22:57:41 GMT

The following script logic will work.

AllEmployees:

load ID from [All Employees];

Left Join Load ID, 1 as Flag from [Resigned Employees];

CurrentEmployees:

NoConcatenate

load ID resident AllEmployees where Flag <> 1;

left Join load * from [All Employees]; // to load all data of each employee

Drop Table AllEmployees;

Anonymous — Tue, 17 Jul 2012 23:45:26 GMT

Venkat,

I'd load the resigned employees first, and load all employees after that with condition

where not exists(<resigned employee id>, <all emplyee id>)

After that, drop the resigned table if it is not needed.

Regards,

michael