4 Replies Latest reply: Apr 20, 2012 4:25 AM by Anne Duffy RSS

    Version Number

      Hi Experts,

       

      (I have touched on this as part of another thread also - Distinct compare 2 diff tables in load)

       

      Quick one, I have a table that will have duplicate entries within 1 of the fields, what I wish to do is count what version it is so I may concatenate it together to ensure I only compare correct timeseriesed accounts - see tables below please

       

       

      Original :

       

      Account        Version Number

      1234               1

      1111               1

      2222               1

      1234               2

      1111               2

      1234               3

      2222               2

       

      If I was doing this in excel I would count(A$2:A2,A2) so I would count from the start to where I am now then Id concatenate account and Version the desired result is below ( I know theres a recno() function, is there an equivalent verno(Account) similar)

       

      Desired result:

       

      Account        Version Number  Search

      1234               1                       12341

      1111               1                       11111

      2222               1                       22221

      1234               2                       12342

      1111               2                       11112

      1234               3                       12342

      2222               2                       22222

       

       

      Can anyone help please ?

       

      Thanks


      A

        • Version Number
          Kaushik Solanki

          Hi,

           

             Can you please tell us the logic..

           

             Cause in your example  only Accout which has version number 3 is concated with older version.

           

             So pls tell whats the logic.

           

          Regards,

          Kaushik Solanki

          • Re: Version Number
            Jonathan Dienst

            Hi

             

            Please check out the attached file. Does it do what you want?

             

             

            // Load source data. RowNo is used to maintain original load order
            
            Data:
            LOAD 
                 Account, 
                 RowNo() As RowNo 
            INLINE
            [
                 Account
                 1234
                 1111
                 2222
                 1234
                 1111
                 1234
                 2222
            ];
            
            // Calculate the sequence number by looping over the data and incrementing 
            // the sequence number for successive accounts with the same value
            
            Sequence:
            LOAD *,
                 Account & Sequence As SearchNo
            ;
            LOAD Account,
                 RowNo,
                 If(Account = Previous(Account), Peek('Sequence') + 1, 1) As Sequence
            Resident Data
            Order By Account, RowNo;
            
            DROP Table Data;
            
            

             

             

            Regards

            Jonathan

            • Version Number
              Rob Wunderlich

              If you want to add sequence numbers to the accounts, I find the easiest way is to add this field in the load:

               

              autonumber(recno(), Account) as [Version Number]

               

              -Rob