5 Replies Latest reply: Aug 1, 2016 2:37 AM by Maxim Skorik RSS

    Customers with "3rd order..."

    Maxim Skorik

      Hello community.

      I have interesting task, in my head I have idea how to solve this, but I not sure that I good enough to put this idea to "script code"...

      So please suggest something...

       

      I need to create a table which have inside customers who have 3rd order in specific month (accordingly to order date)  of specific customer.

      I have raw source data like:

       

      Order number
      Order date (DDMMYYYY)Customer ID
      abc123412042016CustA
      sdf213510022015CustB
      asw345520012013CustA
      ert543213042016CustC
      dfg567817052016CustD
      asd345614052016CustC
      ghj456717072016CustC
      sdf456730012015CustA
      ewr432505072016CustA
      sdf434523052015CustB
      etc.......

      etc....

      etc....

       

      Close to 1 million lines.

      As result  I need to have aggregated table, where only customers who have 3 and more orders included and where is date of 3rd order specified:

       

      CustomerID3rd Order Date
      CustA12042016
      CustC17072016
      etc......

       

      CustB have only 2 orders, CustC have only 1 order so they out of selection...

       

      I prefer to do this table during script load, because after I need to do some extra actions with this resulting table...

       

      As I understand I should sort source table by customer name then sort orders by order date inside every specific customer, then apply autonumber for orders (but autonumber must be applied for orders inside every specific customer, and for every next customer autonumber of orders must start from 1 again). And finally I will simply select all orders with AutoNumberField = 3.

       

      But I almost have no experience with Autonumber function... Please suggest how to realize such transformation...

      Maybe other ideas for transformation logic...

       

      Thank you in advance!