7 Replies Latest reply: May 8, 2013 5:23 AM by Darren Johnson RSS

    Peek

    Darren Johnson

      Hello,

       

      Can anyone advise me on what is wrong with this code/

       

      CustOrders:

      NoConcatenate

      LOAD DISTINCT [Customer ID],[SalesOrderLine Number],[SalesOrderLine Date]

      RESIDENT SalesOrders

      ORDER BY [Customer ID],[SalesOrderLine Date] ASC;

       

      LastOrders:

      NoConcatenate

      LOAD [SalesOrderLine Number], IF(Peek('Customer ID')=[Customer ID],Peek('SalesOrderLine Date'),NULL()) As LastOrderDate

      RESIDENT CustOrders

      ORDER BY [Customer ID], [SalesOrderLine Date] ASC;

       

      DROP TABLE CustOrders;

       

      I want to create a table 'Last Orders' that contains, for each 'SalesOrderLine Number', the date of the previous order placed by the customer (or Null if there was no previous order).

       

      This script currently gives me a null value for every 'LastOrderDate'

       

      Many Thanks

       

      Darren

        • Re: Peek
          ioannis giakoumakis

          try using above :

           

          LastOrders:

          NoConcatenate

          LOAD [SalesOrderLine Number],

          if(above([Customer ID])=[Customer ID], above([SalesOrderLine Date], null()) As LastOrderDate

          RESIDENT CustOrders

          ORDER BY [Customer ID], [SalesOrderLine Date] ASC;

          • Re: Peek
            ioannis giakoumakis

            Sorry not above (works only in charts), you need to use previous:

             

            LastOrders:

            NoConcatenate

            LOAD [SalesOrderLine Number],

            if(previous([Customer ID])=[Customer ID], previous([SalesOrderLine Date], null()) As LastOrderDate

            RESIDENT CustOrders

            ORDER BY [Customer ID], [SalesOrderLine Date] ASC;

            • Re: Peek
              Gysbert Wassenaar

              I think you need to use the previous() function here instead of peek().

              IF(Previous([Customer ID])=[Customer ID],Previous([SalesOrderLine Date]),NULL()) As LastOrderDate

              • Re: Peek
                Darren Johnson

                Thanks for your help,

                It's still not working using the 'Previous' function as below... Any other possibilities?

                 

                CustOrders:

                NoConcatenate

                LOAD DISTINCT [Customer ID],[SalesOrderLine Number],[SalesOrderLine Date]

                RESIDENT SalesOrders

                ORDER BY [Customer ID],[SalesOrderLine Date] ASC;

                 

                LastOrders:

                NoConcatenate

                LOAD [SalesOrderLine Number], IF(Previous('Customer ID')=[Customer ID],Previous('SalesOrderLine Date'),NULL()) As LastOrderDate

                RESIDENT CustOrders

                ORDER BY [Customer ID], [SalesOrderLine Date] ASC;

                 

                DROP TABLE CustOrders;

                  • Re: Peek
                    ioannis giakoumakis

                    a sample application would help

                    • Re: Peek
                      Henric Cronström

                      I can see several problems here...

                      • If you sort Ascending, you will get the first order, not the last.
                      • Peek does not work since you do not have [Customer ID] in the output. So Gysbert is right: Use Previous() instead.
                      • You cannot use 'Customer ID' inside Previous(). You need to quote it with brackets or double quotes: [Customer ID]
                      • The first record within a Customer ID gets NULL as LastOrderDate. But you want it to be a Date, don't you?

                       

                      Bottom line, I would use

                      LOAD

                           [SalesOrderLine Number],

                           IF(Previous([Customer ID])=[Customer ID],Peek([LastOrderDate]),[SalesOrderLine Date]) As LastOrderDate

                      RESIDENT CustOrders

                      ORDER BY [Customer ID], [SalesOrderLine Date] DESC;

                       

                      HIC