topic Re: Qlik Sense Loading one table with overriding values from another table in App Development

Qlik Sense Loading one table with overriding values from another table

semgem12 — Mon, 14 Jan 2019 14:26:59 GMT

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.

Re: Qlik Sense Loading one table with overriding values from another table

marcus_sommer — Mon, 14 Jan 2019 14:48:29 GMT

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

Re: Qlik Sense Loading one table with overriding values from another table

JustinDallas — Mon, 14 Jan 2019 15:00:02 GMT

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

Re: Qlik Sense Loading one table with overriding values from another table

JustinDallas — Mon, 14 Jan 2019 15:01:00 GMT

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
;

Re: Qlik Sense Loading one table with overriding values from another table

semgem12 — Tue, 15 Jan 2019 10:49:35 GMT

Thanks Marcus, that works fine.