topic Re: Finding Mismatch data in Visualization and Usability

Finding Mismatch data

Leo_123 — Sat, 16 Nov 2024 17:03:12 GMT

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.

Re: Finding Mismatch data

Kushal_Chawda — Tue, 27 Jul 2021 22:16:27 GMT

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

Re: Finding Mismatch data

Leo_123 — Wed, 28 Jul 2021 00:11:03 GMT

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 🙂

Re: Finding Mismatch data

avinashelite — Wed, 28 Jul 2021 02:04:36 GMT

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 ?

Re: Finding Mismatch data

Leo_123 — Wed, 28 Jul 2021 05:41:33 GMT

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.

Re: Finding Mismatch data

Leo_123 — Wed, 28 Jul 2021 05:42:09 GMT

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

Re: Finding Mismatch data

Kushal_Chawda — Wed, 28 Jul 2021 21:47:36 GMT

@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;

Re: Finding Mismatch data

Leo_123 — Wed, 28 Jul 2021 22:40:21 GMT

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.

Re: Finding Mismatch data

Kushal_Chawda — Wed, 28 Jul 2021 23:19:32 GMT

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

Re: Finding Mismatch data

Leo_123 — Tue, 03 Aug 2021 10:25:28 GMT

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.