2 Replies Latest reply: May 16, 2016 1:29 PM by fredrik olsson RSS

    Add extra markup to order data

    fredrik olsson

      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

        • Re: Add extra markup to order data
          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.

            • Re: Add extra markup to order data
              fredrik olsson

              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 ?