load missing values / discrepancy in values

[bilzekek]

Hi,

I have two qvd files / tables with data that i want to compare. Source.qvd and target.qvd

in each table I have a customer number and customer name . The data in source.qvd will always be correct but there may be error in data found in target.qvd. I want to find all where customer name is incorrect in target.qvd and show in a table. For example i may have customer ID 232323 with name "example1" in source.qvd and in target.qvd it may be ID 232323 and name "example2".  

The solution I have tried in script is:

// first I load the two files to get an overview of data

Table_source:

LOAD 

              Text(Trim(sourcename)) as Source_name,

              Text(Trim(sourcenumber)) as Source_number

              Text(Trim(sourcenumber)) as %key

FROM

[source.qvd] (qvd);

Table_target:

LOAD 

              Text(Trim(targetname)) as Target_name,

              Text(Trim(targetnumber)) as Target_number

              Text(Trim(targetnumber)) as %key

FROM

[target.qvd] (qvd);

// I then load data again into a missing_name table to find discrepancies

Table_missing_name:

              Text(Trim(sourcename)) as missing_name,

              Text(Trim(sourcenumber)) as %key

FROM

[source.qvd] (qvd) where not Exists(Target_name, Text(Trim(sourcename)));

----------------------

This script above actually works pretty well but - somehow - it does not find all errors. I have used excel to check the above mentioned script and it seems that some clear error in names is not catched (the table_missing_name is empty for that particular customer ID).

Can someone help and to find what is wrong here?

Thank you in advance!

[saminea_ANZ]

Script quite okay to me, What is the issue on below?

---

@bilzekek wrote:

somehow - it does not find all errors. I have used excel to check the above mentioned script and it seems that some clear error in names is not catched (the table_missing_name is empty for that particular customer ID).

---

[bilzekek]

When I checked the data manually in excel using =EXACT function, i Ifind discrepancies in customer name that Qlikview does not identify using this script.

So the issue is that the script does not return a table with all errors that exist between the data and I dont know why.

[asinha1991]

can you  share the minimum viable data set that demonstrate the issue?

is it possible that name might not be allocated to same number but still there in the list?

in that case use exist on concatenation of number and name. 

[bilzekek]

It will be dificult to share the dataset, so I hope that this screenshot demonstrates the issue:

I have selected 3 customer ID's from source.qvd that also are found in target.qvd. 1 has correct name and should not be found by the table_missing_name. Another is incorrect and is also found by the table_missing_name. The last (purple) is also incorrect but is not identified by the table_missing_name..

[bilzekek · Accepted solution]

Hello again,

I think I found another way to do this using Straight table instead.

Thank you for your time anyway 🙂