topic Determining matches between two linked tables in QlikView

Determining matches between two linked tables

Thu, 10 Sep 2009 03:19:53 GMT

Hi all. I've got two tables where one refers to the other. One table is a product record table, and the other is an invoice table that has a reference to the product ID. I have some invoices in my invoice table that don't refer to valid product IDs and I want to pull them out. How can I create a derived field on the invoice to say whether or not it matches a valid product ID? I was thinking to use a mapping table but I can't think of the right way to do it. Thanks!

Eric

Determining matches between two linked tables

Thu, 10 Sep 2009 03:37:15 GMT

If the tables are linked and the Product ID from the Invoice table does not match a valid Product ID from the Product table, then any Product fields associated with that invoice number will be Null.

Create a table with Invoice Number and Product ID as the dimensions. Then pick a field from the Product table (anything other than Product ID should do). Then create an expression like:

If (IsNull(ProductField), 1, 0)

If Suppress Zero Values is checked in the properties, then only those invoices with unmatched Product IDs will show up.

Determining matches between two linked tables

prieper — Thu, 10 Sep 2009 04:18:43 GMT

Good day,
it is wise to replace the ISNULL-function with LEN(TRIM(Field)) = 0 as this would also work on 64-bit-machines.

HTH
Peter