topic Re: load missing values / discrepancy in values in QlikView

load missing values / discrepancy in values

bilzekek — Mon, 15 Apr 2019 08:53:23 GMT

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!

Re: load missing values / discrepancy in values

saminea_ANZ — Mon, 15 Apr 2019 09:10:35 GMT

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).

Re: load missing values / discrepancy in values

bilzekek — Mon, 15 Apr 2019 09:15:30 GMT

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.

Re: load missing values / discrepancy in values

asinha1991 — Mon, 15 Apr 2019 09:16:09 GMT

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.

Re: load missing values / discrepancy in values

bilzekek — Mon, 15 Apr 2019 09:32:55 GMT

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..

Re: load missing values / discrepancy in values

bilzekek — Mon, 15 Apr 2019 09:55:38 GMT

Hello again,

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

Thank you for your time anyway 🙂