Need help with joining 2 tables, Values are not matching

[Anonymous]

Hello,

I have Customer table and Invoice table, they are joined on Customer_id. Customer name is also common between these 2 tables.

Users want to filter data on Customer name.

The issue am facing here is if i take customer name from Customer table in the search object i get the count(costumer_id) right but the invoice amount is messed up. And if i take customer name from Invoice table i get the invoice amount right but the count(costumer_id) is messed up.

1.I tried a left join on Invoice table to Customer table as every Invoice must and should have a customer_id. Here i got the count(costumer_id) right but the Invoice amount is almost tripled.

2.Also I tried creating a linked key on Customer_id and Customer_Name. Here both count(costumer_id) and Invoice amount is more than expected

3.Outer join didn't work either, the application size bumped to twice its size.

4.Applymap didn't help much,taking forever to reload.

Can someone please suggest me how to fix this issue asap.

Thank You!

[Oleg_Troyansky]

It sounds like you have some bad data that you need to troubleshoot and possible cleanse.

1. If JOIN load causes tripling the invoice data, it sounds like your Customers table is not distinct - you likely have several (three in average) rows for each Customer ID.

2. If you really want to keep the Customer Name in both tables, you have to rename one of them. However, when you resolve the issues with bad data, you won't need to keep both fields.

3. As a diagnostics, open the Table Viewer and hover over the key field Customer_id in both tables. Notice the value of the Subset Ratio for both tables.

- Ideally, both tables should have 100% Subset Ration.

- If you see 100% in the Invoices table and a lower % in the Customers Table, then it means that some invoices don't have customers.

- If you see 100% in the Customers table and a lower % in the Invoices table, then it means that all invoices have a customer, but not all customers have invoices. That's not very bad.

- if you see both numbers below 100%, then you might have a bit of each problem.

- If the two numbers add up to 100% (like for example 72% and 28%) then your key fields are not matching. You might have a number and a string with the same numeric value, but the two won't match.

hopefully these ideas can help you troubleshoot your issues.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[evan_kurowski]

From your description it sounds like the table relationship needs to look like this (no joins in the scripting required). 

As for the user interface calculations, it sounds like your customer to invoice data has a many to many relationship.

Selecting one customer will not reconcile to the amount of a single invoice, but all that customer's potential invoices, if you make a selection of one distinct customer & invoice and the amount doesn't reconcile, then something indeed is wrong.

If you choose a single invoice and get a larger than expected result from Count(CUSTOMER_ID), you need to add DISTINCT to your syntax, as you are probably tabulating all the times the CUSTOMER_ID appears in the INVOICES table.

[Anonymous]

I appreciate your quick reply!

I see 100% in the Customers table and a 97 % in the Invoices table,as you said it means all invoices have a customer, but not all customers have invoices.

Also I renamed the customer_name in Invoice table to Invoice_Customer_Table.

Is it possible to use Customer_Name from Customers table in the search object and limit the Invoice amount by Customer_Name from Invoice Table?

[Anonymous]

If I use distinct on Count(Customer_id) the result is lower than expected.

And yes, without Joins the table relation looks like above with Invoice_Customer_Name in Invoices table.

What could be the issue here?

[Oleg_Troyansky]

You only need one instance of the field Customer Name. If you prefer to store it in the Invoices table and not in the Customers table, it's fine.

I still think that your table of Customers has duplicated records. Use Preview to see "raw" data in order to determine that.