Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
Thank you, great solution!