Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Comparing fields in separate tables

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

2 Replies
swuehl
MVP
MVP

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;

Not applicable
Author

Thank you, great solution!