9 Replies Latest reply: Jun 18, 2015 5:36 PM by Minh-Tri Truong RSS

    Tag order as first order

    Minh-Tri Truong

      Hello,

       

      I am using QlikSense.

       

      I have an excel spreadsheet with all the orders lines (so 1 order can spread on multiple lines).

       

      OrderID,

      Contact_Email,

      Line_amount,

      OrderDate

       

      My goal is to be able to tag the first orders for each client ==> From my table I can now get the customer data with the first order date and the last order date. Therefore if the order_date = customer_first_order_date then I can flag the order as a "first order".

       

      Could you help me doing that?

      --------------------------------------------

      As of now, here is the script I have :

       

      Facts:

      LOAD

          OrderID,

          DATE(FLOOR(FIRST_INVOICE_DATE)) as OrderDate,

          If(LINE_TYPE='Product',QUANTITY,0) as ProductRecordCount,

          CONTACT_EMAIL,

          Line_amount

      FROM [lib://DataFiles/InvoiceDetail.xlsx]

      (ooxml, embedded labels, table is [InvoiceDetail]);

       

      OrderData:

      LOAD

          OrderID,

          date(min(OrderDate)) as invoice_date,

          1 AS OrderRecordCount,

          sum(Line_amount) as CA_HT_NET,

          count(OrderID) as OrderLinesCount

      RESIDENT Facts

      group by OrderID;

       

      CustomerData:

      LOAD

          CONTACT_EMAIL,

          date(min(OrderDate)) as first_order_date,

          date(max(OrderDate)) as last_order_date,

          count(distinct(OrderID)) as nborders

      RESIDENT Facts

      group by CONTACT_EMAIL;

       

      ==> At this step, I would like to add the field IsFirstOrder in the OrderData table (but may be this is not the right way to do that, feel free to propose me your solution ;-))

       

      Thank you in advance for your help.

       

      Warm Regards,

       

      MT