Qlik Sense Loading one table with overriding values from another table

[semgem12]

Hello, hope someone can help please.

I have a table of invoices and a table of invoice adjustments (both QVDs). I would like to be able to load the invoices in Qlik Sense and then load the adjustments so that the invoice values in the adjustment table replaces those in the invoice table. The invoice table has many columns including InvoiceNumber, ProductNumber, Value. The adjustment table has just InvoiceNumber, ProductNumber, AdjustmentValue.

 E.g.

Suppose the invoice table had an invoice for a product for a value of 100.00 and the adjustment table had a row for the same invoice number and product number with an adjustment value of 75.00. I would like the result to be that for this invoice and product the value is 75.00, not the original 100.00.

Thanks.

[marcus_sommer · Accepted solution]

You could use an approach like the following:

m: mapping load InvoiceNumber & '|' & ProductNumber, AdjustmentValue from adjustment.qvd (qvd);

invoice:
load *, applymap('m', InvoiceNumber & '|' & ProductNumber, Value) as ValueNew from invoice.qvd (qvd);

- Marcus

[JustinDallas]

I like this solution, much better than the one I created.

[JustinDallas]

An inferior solution to the one offered by Marcus would be something like the following.  It's more busywork than anything else.

Invoices:
LOAD *,
	"InvoiceNumber" & '/' & "ProductNumber" AS '%invoice_adjustment_key'
;
LOAD * Inline
[
	'InvoiceNumber', 'ProductNumber', 'Value'
    123, GoodProduct-34, 100
    345, GoodProduct-356, 239
]
;

InvoiceAdjustments:
LOAD "AdjustmentValue",
	"InvoiceNumber" & '/' & "ProductNumber" AS '%invoice_adjustment_key'
;    
LOAD * Inline
[
	'InvoiceNumber', 'ProductNumber', 'AdjustmentValue'
    123, GoodProduct-34, 75
]
;

DROP FIELDS InvoiceNumber, ProductNumber FROM InvoiceAdjustments
;

LEFT JOIN(Invoices)
LOAD * Resident InvoiceAdjustments
;

DROP TABLE InvoiceAdjustments
;

Invoices2:
LOAD *, If( NOT ISNULL(AdjustmentValue) AND AdjustmentValue > 0, 
				AdjustmentValue, Value
		) AS 'Adjusted Value'
RESIDENT Invoices        
;
DROP TABLE Invoices
;
DROP FIELD Value FROM Invoices2
;
RENAME FIELD "Adjusted Value" TO 'Value'
;

exit script
;

[semgem12]

Thanks Marcus, that works fine.