9 Replies Latest reply: Mar 18, 2015 4:35 AM by Nayan Lalla RSS

    Previous Document Number

    Nayan Lalla

      Hi

       

      We process receipt transactions and i want to display the previous document number.  so for eg in the table below,

      for  Item A, I want to display RCP005 ,

      for Item B, I want to display RCP032,

      for Item C, I want to display RCP024

       

      How do i do this?

       

      Thank you

      regards

      Nayan

       

      ItemDocument Number
      ARCP005
      ARCP017
      BRCP012
      BRCP026

      B

      B

      RCP032

      RCP045

      CRCP018
      CRCP024
      CRCP033
        • Re: Previous Document Number
          Ruben Marin

          Hi Nayan, if you add a Row number to document number, and they are sorted by Item and Document number, ie for this data:

          Data:

          LOAD *, RowNo() as RowNumber;

          LOAD * Inline [

          Item,Document Number

          A,RCP005

          A,RCP017

          B,RCP012

          B,RCP026

          B,RCP032

          B,RCP045

          C,RCP018

          C,RCP024

          C,RCP033

          ];

           

          You can use this expression:

          FirstSortedValue([Document Number], -RowNumber, 2)

            • Re: Previous Document Number
              Nayan Lalla

              Hi Ruben

               

              Thank you for your prompt reply.  Will try it out.  Not sure if it will work.  Let me just elaborate on my query. When we receive stock, it must show the current documnet number and unit cost.  Also on the same line, i want to display what our previous receipt cost and document was.

               

              Let me know if you understand .  if not , i can upload a mock qlikview model.

               

              kind regards

              Nayan

                • Re: Previous Document Number
                  Ruben Marin

                  Ok, you can use Previous() or Peek() functions. Again you have to start with a table where your data is sorted by type and document number (or by date of that document number)

                   

                  Data:

                  LOAD *, If(Previous(Item)=Item, Previous([Document Number])) as PrevDocument, RowNo() as RowNumber;

                  LOAD * Inline [

                  Item,Document Number

                  A,RCP005

                  A,RCP017

                  B,RCP012

                  B,RCP026

                  B,RCP032

                  B,RCP045

                  C,RCP018

                  C,RCP024

                  C,RCP033

                  ];

                   

                  So if this reads an Item equal to the previous Item, it gets the previous document number and saves in PrevDocument field.

                    • Re: Previous Document Number
                      Nayan Lalla

                      Hi Ruben

                       

                      Thank you for your reply.   Unfortuantely ,  In my actual database , is not sorted by type and document number.  Will have a try at your formula and will let you know.

                       

                      regards

                      Nayan

                        • Re: Previous Document Number
                          Ruben Marin

                          Hi Nayan, if you have some kind of date or another field to give you wich order should be for [Document Number] or if this [Document Number] is the current order you can do something like:

                           

                          data: // Your current load

                          LOAD...

                          Select....

                           

                          PrevDocuments:

                          LOAD Item,

                               [Document number],

                               If(Previous(Item)=Item, Previous([Document Number])) as PrevDocument

                          Resident data order by Item, [Document number];

                           

                          Left Join (data) LOAD * Resident PrevDocuments;

                          DROP Table PrevDocuments;

                           

                          If it's only one record for each Item and document number it should work.

                            • Re: Previous Document Number
                              Nayan Lalla

                              Hi Ruben

                               

                              Thank you .  Will try it out.  Much appreciated. By the way, this is the first time im aware of the "Previous function" .

                               

                              kind regards

                              Nayan


                                • Re: Previous Document Number
                                  Nayan Lalla

                                  Hi Ruben

                                   

                                  Your scripting worked fine. Thank you.

                                  Sorry for the delayed reply, I left for home.

                                  Just one more question, in my actual Qlikivew model, I have unit cost per receipt per item. How do i link this unit cost  to the "previous document".

                                   

                                  so for eg, for item C, for each receipt below there is a unit cost

                                   

                                  RCP018  - $20.00

                                  RCP024 - $22.00

                                  RCP033 - $25.00

                                   

                                  So the result i would like to achieve is RCP024 with unit cost of $22.00.

                                   

                                  kind regards

                                  Nayan