Compare two fields in two different tables from two different sources

[elie_issa]

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.

[agigliotti]

could you provide a sample app with mock data?

[vamsee]

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]

This article could help you.

Don't join - use Applymap instead

[sujeetsingh · Accepted solution]

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]

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]

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;