7 Replies Latest reply: Feb 16, 2018 10:54 AM by Peter Cammaert RSS

    Use Peek/Previous from 2 tables

    Mark Graham

      Hi All -

       

      I have 2 diffferent tables which i cannot join into one table due to my data structure.

       

        

      Table1
      ItemUnits
      A10
      B20
      C30
      D40
      E50    

       

      Table2
      OrderDateOrderItemReq
      2/14/2018ABCA10
      2/14/2018ABCB20
      2/15/2018DEFA10
      2/15/2018GHIC30

           

      Req O/P:
      OrderDateOrderItemReqFlag
      2/14/2018ABCA10Y
      2/14/2018ABCB20Y
      2/15/2018DEFA10N
      2/15/2018GHIC30

      Y

       

       

      I wanted to start allocating my units by oldest order date first.

      Once allocated, i wanna subtract the allocated units from units it doesnot get allocated in a duplicate way.

      Once the units cannot fill 'req' field, 'N' will populate, else 'Y'

       

      Can someone please help??

      Please note that i just want to lookup the value from Table1, but not join it.

      Wanted to do this in script.

       

      Any help is highly appreciated.

        • Re: Use Peek/Previous from 2 tables
          Sunny Talwar

          So, if the data was something like this

           

          Table2
          OrderDateOrderItemReq
          2/14/2018ABCA5
          2/14/2018ABCB20
          2/15/2018DEFA5
          2/15/2018GHIC30

           

          The output would have been this?

           

          OrderDateOrderItemReqFlag
          2/14/2018ABCA10Y
          2/14/2018ABCB20Y
          2/15/2018DEFA10Y
          2/15/2018GHIC30

          Y

           

          Also, what would be the output for a scenario like this

           

          Table2
          OrderDateOrderItemReq
          2/14/2018ABCA5
          2/14/2018ABCB20
          2/15/2018DEFA8
          2/15/2018GHIC30
          • Re: Use Peek/Previous from 2 tables
            Vikraant Koushika Pai

            Hi Mark,

             

            As far as I have understood the problem, what is required is, if total number of units of item in table 2 exceeds that mentioned in table 1 then flag should be 'N' . If the number of units of item in table 2 is less than that in table 1 , then the flag should be 'Y'.  And this should be sorted as per the date.

             

            Try out the following logic :

             

            //------------------------------------------------------------------------------------------------------------------------------

            //Assuming one to one mapping for units

            Table1 :

            Mapping Load * Inline

             

            [

            Item,Units
            A,10
            B,20
            C,30
            D,40
            E,50    

            ];

             

             

             

            [Req O/P]:

            Load

                OrderDate,

               Order,

                Item,

                Req ,

              if( Item = Peek(Item)  , Peek(Req_Cumm) +  Req ,  Req ) as Req_Cumm,

              if( Req_Cumm  >  ApplyMap('Table1', Item, 0), 'N', 'Y')   as Flag

             

            Resident Table2  Order By OrderDate, Order, Item ;

             

            Drop Table Table2;

             

            //------------------------------------------------------------------------------------------------------------------------------

             

            Best Regards,

            Vikraant

            • Re: Use Peek/Previous from 2 tables
              Peter Cammaert

              Order Fulfillment?

               

              Joining your stock to those Order rows is pretty much the easiest way to get to your end-result. Keep in mind that in the end such a solution wouldn't force you to keep any superfluous fields in your order table. Just the Flag field.

               

              As possible methodology:

               

              • Create a mapping table that maps each product ot its stock level.
              • Take your orders table, and order it by product and date. Use an ORDER BY clause when performing the next two steps. ORDER BY happens before anything else.
              • At the same time, whenever the previous product is different from the current one, lookup the initial stock value using the mapping table and set Flag=Y if you have enough stock and Flag=N if the initial stock cannot fulfill the requested amount. Store the remaining stock in a new column.
              • If the current product is the same as the previous product, compare the remaining stock of the previous row to the ordered amount. Set Flag=Y if you still have enough stock and Flag=N if the previous stock cannot fulfill the requested amount. Store the remaining stock in a new column.


              Now remove the Remaining stock column and reorder your table as you like. IS that possible in your data model?


              Peter