Add extra markup to order data

[flygstolen_fred]

Hi,

I have a set of order tables (Order, Payment & Invoice) in SQL visualized in Qlik Sense but now I wanna add invoice rows with "external" markups (cost / revenue) to each order. Each order could match multiple markups so I need to iterate in some way.

OrderID	OrderDate	CustomerID	ShopID
1234	2016-01-01	5	2
1245	2016-02-02	7	5

PaymentID	PaymentCode	PaymentAmount	OrderID
23444	VISA	100	1234
23547	MC	150	1245

InvoiceID	ProductCode	Name	Price	Cost	OrderID
465444	WNF	Wallposter	50	45	1234
456477	UEO	Frame	50	40	1234
465789	IJD	Wheels	150	90	1245

I have external markups in a separate table that I wanna connect to each order when a match is found. For the first order(1234) I wanna add a payment cost and for the second order (1245) I wanna add a extra kickback for selling a specific product. The extra markup should be added as new Invoice items. Every markup has a from- and toDate and a specified ShopID.

MarkupType	RefCode	Price	Cost	Name	ShopID	FromDate	ToDate
PAYMENT	VISA	0	5	Payment cost	2	2016-01-01	2016-12-31
PAYMENT	VISA	0	7	Payment cost	1	2016-01-01	2016-12-31
INVOICE	IJD	15	0	Extra kickback for selling wheels	5	2016-01-01	2016-12-31

Could anyone point me in the right direction, any help would be appreciated.

Meddelandet redigerades av: fredrik olsson

[sunny_talwar]

Like this?

I used Extended Interval Match concept to link Orders Table to External Markup table

IntervalMatch

IntervalMatch (Extended Syntax) ‒ QlikView

Qlikview Tutorial: Extended IntervalMatch

Script:

Order:

LOAD

    OrderID,

    OrderDate,

    CustomerID,

    ShopID

FROM [lib://Lib]

(html, codepage is 1252, embedded labels, table is @1);

Payment:

LOAD

    PaymentID,

    PaymentCode,

    PaymentAmount,

    OrderID

FROM [lib://Lib]

(html, codepage is 1252, embedded labels, table is @2);

Invoice:

LOAD

    InvoiceID,

    ProductCode,

    Name,

    Price,

    OrderID

FROM [lib://Lib]

(html, codepage is 1252, embedded labels, table is @3);

ExternalMarkup:

LOAD

    MarkupType,

    RefCode,

    Price as ExternalMarkupPrice,

    Cost,

    "Desc",

    ShopID,

    FromDate,

    ToDate

FROM [lib://Lib]

(html, codepage is 1252, embedded labels, table is @4);

Left Join (Order)

IntervalMatch(OrderDate, ShopID)

LOAD FromDate,

  ToDate,

    ShopID

Resident ExternalMarkup;

Data model:

You will see a synthetic key, but as per HIC's blog, this isn't a bad thing. So don't worry about this.

[flygstolen_fred]

Hi Sunny T,

Thanks for helping out.

Maybe my explanation was little bit unclear.

I wanna add the external markup as new invoicelines to every booking in the same format as the existing one.

(The existing invoicelines also has a cost column that I didn't specify in the posting.)

This is how I think the result show look like:

InvoiceID	ProductCode	Name	Price	Cost	OrderId
EXT	IJD	Extra kickback for selling wheels	15	0	1245
EXT	VISA	Payment cost	0	5	1234

How do for example make sure that the payment cost only gets applied if it has RefCode = VISA and not MC ?