Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
flygstolen_fred
Creator
Creator

Add extra markup to order data

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.

OrderIDOrderDateCustomerIDShopID
12342016-01-0152
12452016-02-0275

PaymentIDPaymentCodePaymentAmountOrderID
23444VISA1001234
23547MC150

1245

InvoiceIDProductCodeNamePriceCostOrderID
465444WNFWallposter50451234
456477UEOFrame50401234
465789IJDWheels150901245

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.

MarkupTypeRefCodePriceCostNameShopIDFromDateToDate
PAYMENTVISA05Payment cost22016-01-012016-12-31
PAYMENTVISA07Payment cost12016-01-012016-12-31
INVOICEIJD150Extra kickback for selling wheels52016-01-012016-12-31

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

Meddelandet redigerades av: fredrik olsson

2 Replies
sunny_talwar

Like this?

Capture.PNG

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:

Capture.PNG

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
Creator
Creator
Author

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:

InvoiceIDProductCodeNamePriceCostOrderId
EXTIJDExtra kickback for selling wheels1501245
EXTVISAPayment cost051234

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