Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

flygstolen_fred
New Contributor III

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

Re: Add extra markup to order data

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
New Contributor III

Re: Add extra markup to order data

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 ?

Community Browser