topic Comparing fields in separate tables in QlikView

Comparing fields in separate tables

Sat, 28 Nov 2015 18:41:02 GMT

Hi,

I got an issue I can't seem to fix despite Googling. I have two tables defined in my load script;

-> Table Billing_Documents

-> Table Sales_Orders

Both contain the key 'SALES_DOCUMENT' and 'SALES_DOCUMENT_ITEM'. Now I want to add a third table which compares the unitprice between order and invoice on item-level. SALES_DOCUMENT and SALES_DOCUMENT_ITEM should be the unique key together.

Currently I'm trying to do as follows, however this doesn't seem to do anything (BILLING_ITEM_NET_PRICE comes from table Billing_documents, SO_ITEM_NET_PRICE is from table Sales_orders);

COMPARE_TAB:

load

SALES_DOCUMENT,

SALES_DOCUMENT_ITEM,

BILLING_ITEM_NET_PRICE,

SO_ITEM_NET_PRICE,

IF(SO_ITEM_NET_PRICE=BILLING_ITEM_NET_PRICE,'Yes','No') As Compare;

What am I doing wrong?

Re: Comparing fields in separate tables

swuehl — Sun, 29 Nov 2015 02:30:59 GMT

You either need to join both tables into one, so you can access the required price fields within the same LOAD, or you need to Lookup() or ApplyMap() the price values for the keys from one table into the other.

CheckPerJOIN:

LOAD

SALES_DOCUMENT,

SALES_DOCUMENT_ITEM,

BILLING_ITEM_NET_PRICE

FROM Billing_Documents;

INNER JOIN

LOAD

SALES_DOCUMENT,

SALES_DOCUMENT_ITEM,

SO_ITEM_NET_PRICE

FROM Sales_Orders;

COMPARE:

LOAD

SALES_DOCUMENT,

SALES_DOCUMENT_ITEM,

BILLING_ITEM_NET_PRICE,

SO_ITEM_NET_PRICE,

IF(SO_ITEM_NET_PRICE=BILLING_ITEM_NET_PRICE,'Yes','No') As Compare

RESIDENT CheckPerJOIN;

DROP TABLE CheckPerJOIN;

Re: Comparing fields in separate tables

Sat, 05 Dec 2015 15:22:48 GMT

Thank you, great solution!