Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
elie_issa
Creator II
Creator II

Compare two fields in two different tables from two different sources

Hello,

I am trying to make a comparative analysis between our data found in the database and another data from excel file.

key i am using between the two tables is company and need to get all the employees found in the excel and not found in our table (if we can we need to highlight them in red in the table attached)

what i am able to do for now is to show 2 separate tables showing the numbers from our database in table and the numbers from excel in another table.

What i am not able to do is to highlight the employee number found in the excel and not found in the database.

Regards.

1 Solution

Accepted Solutions
sujeetsingh
Master III
Master III

There a number of ways to achieve it;

1. Use applymap function and map those employees which are in excel but not in database.

2. Use Keep to achieve it understand what keep does and then implement it.

3. Use full outer join of both datasets and then apply if condition as if (DB.Emplyee is not null and EXCEL.Emplyee is null then "Exist in DB but not in Excel....)

Let me know if you need mode defined track to achieve it.

Thanks,

View solution in original post

6 Replies
agigliotti
Partner - Champion
Partner - Champion

could you provide a sample app with mock data?

vamsee
Specialist
Specialist

Try, creating a flag as follows.

EmployeeMap:

Mapping Load

EmployeeNumber,

1 as Flag

From Excel;


Database:

LOAD *,

Applymap('EmployeeMap', EmployeeNumber, 0) as Flag_Excel

From Database;


In the chart flag the employees based on Flag=0.


MarioCenteno
Creator III
Creator III

This article could help you.

Don't join - use Applymap instead

sujeetsingh
Master III
Master III

There a number of ways to achieve it;

1. Use applymap function and map those employees which are in excel but not in database.

2. Use Keep to achieve it understand what keep does and then implement it.

3. Use full outer join of both datasets and then apply if condition as if (DB.Emplyee is not null and EXCEL.Emplyee is null then "Exist in DB but not in Excel....)

Let me know if you need mode defined track to achieve it.

Thanks,

elie_issa
Creator II
Creator II
Author

The Applymap is working in a global general way.

What i mean is that using Applymap is showing me if the employee is found in my database and not found in excel

But in my case (check the attached .png in the above) the employee number could be found in both database and excel but in my database the disabled flag could be set as 'NO' and in the Excel file could be 'Yes' so in this case i need to show in the table that the employee number in my database is not disabled.

Hope I clarified my point

Thanks for the help.

vamsee
Specialist
Specialist

Okay, in that case, create one more applymap and flag with these conditions.

Active_Flag_Map:
Mapping Load
EmployeeNumber,
'Yes'
as Flag
From Excel
Where
Disabled='Yes';
Database:
Load
*,
IF(DB_Disabled_Flag='NO',
IF( ApplyMap('Active_Flag_Map', EmployeeNumber,'NO')='Yes',1,0)) as DB_Active_Excel_InActive_Fllag
From Database;