Finding Mismatch data

[Leo_123]

Hello , 

I am new to Qlik sense, I am trying to solve this problem but i am not getting the result i wanted, I have attached the sheet below please check for better understanding 🙂

I have imported data from two data sources:

1. DB ,

2.QVD

In DB , I have supplier Information, Business Unit, Division.

In Qvd, I have sales of Suppliers in respective Business Unit and Division. 

In the DB, the Supplier Business unit and Division should be the one which has highest sales,

For Example, In the attached sheet, For Supplier 1 , the highest sales are in "ES PG G OS" Business Unit,  So the final business unit i.e., in ASD should be the same.

And For supplier 3, the highest sales are in Business Unit "ES PG I RP"  but the Business unit Displayed for this particular Supplier is "ES PG T OS" , Which is not correct. So we need to find such mismatches or wrongly assigned values also for the division. 

I have tried to use IF STATEMENT with Max(Sales in Ponds)) it did work but it just  said "Exceeded memory limit" , if i am just calculating for three suppliers it is working. I have over 15k suppliers in my DB. 

I was wondering if there is any function to compare and just get the mismatches . Please help me with the following  Query. 

Thank you for your time. 

[Kushal_Chawda]

@Leo_123  Do you want to do it in script or front end?

[Leo_123]

Hello ,

I want to do which ever way s the easiest and loads quickly . In chart I tried but it takes a lot of time .

Thank you 🙂

[avinashelite]

I am bit confused on the below case , I have highlighted ...here the division is correctly mapped rite but still you have indicated as mis match could you explain on the this ? 

[Leo_123]

Hello , sorry that is a mistake from my side, I have the correct files now attached.

these two are different data sources, In db, we have supplier in one line and in QVD multiple rows for one supplier.

[Leo_123]

****Correct Sample File******

[Kushal_Chawda]

@Leo_123  try below

qvd:
LOAD Supplier_ID, 
     [Sales in Ponds], 
     [Bussiness Unit], 
     Division
FROM
[C:\Suppliers.xlsx]
(ooxml, embedded labels, table is qvd);

Left Join(qvd)
LOAD Supplier_ID,
     max([Sales in Ponds]) as max_Sales
Resident qvd
Group by Supplier_ID;

Left Join(qvd)
LOAD Supplier_ID, 
     [Business Unti] as BU_DB, 
     Division as Division_DB
FROM
[C:\Suppliers.xlsx]
(ooxml, embedded labels, table is db);

Left Join(qvd)
LOAD Supplier_ID,
     if(lower(trim(BU_DB))=lower(trim([Bussiness Unit])),'match','mismatch') as match_bu
Resident qvd
where [Sales in Ponds]=max_Sales;

DROP Fields max_Sales,BU_DB,Division_DB;

[Leo_123]

Hello Kushal , 

I would prefer to do it in the load script. As i have already tried to do it in Front end and did not work.

[Kushal_Chawda]

@Leo_123  I have given you load script logic only. Please refer to my previous reply

[Leo_123]

Hello Kushal,

I have tried the above script but it takes so long and it just says " Memory limit exceeded" when I use it, Because there are so many suppliers, Is there another way of Doing it? 

Thank you for the help.