5 Replies Latest reply: Aug 20, 2014 11:18 AM by Shenne Tannavi RSS

    Rowno() help

    Byron Van Wyk

      Hi Guys,

       

      I want to add a function in the script that adds a row number for each record in the table that it is defined. How would I do this. I seem to get the same row number appearing multiple times when I use rowno() in the script. I can't upload a QV document as this model has some confidential material. Let me know if there is anything else that you need from me to assist in clarification.

       

       

      Cheers,

      Byron

        • Re: Rowno() help
          Miguel Angel Baeyens de Arce

          Hi Byron,

           

          If you don't have any issues concatenating, joining or so, RowNo() should work just fine, returning "1" for the first record loaded, and increasing by 1 for each record onwards in the same table meaning that two tables will have twice the enumeration. You can use RecNo() function as well. Check the different behaviors according to the following script:

           

          Table1: // Will add a different number per row
          LOAD Chr(64 + Ceil(Rand() * 10)) AS ID,
                     RowNo() AS RowNo
          AUTOGENERATE 10 WHERE Peek('ID') <> 'A';
          CONCATENATE
          LOAD Chr(64 + Ceil(Rand() * 10)) AS ID,
                     RowNo() AS RowNo
          AUTOGENERATE 10;
          
          Table2: // If the record is the same, the RecNo returned is the same
          LOAD Chr(64 + Ceil(Rand() * 10)) AS ID2,
                     RecNo() AS RowNo2
          AUTOGENERATE 10 WHERE Peek('ID2') <> 'A';
          CONCATENATE
          LOAD Chr(64 + Ceil(Rand() * 10)) AS ID2,
                     RecNo() AS RowNo2
          AUTOGENERATE 10;
          

           

          Hope that helps.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

          • Re: Rowno() help

            i have two columns one is ID and second is Date. My requirement is that i have to find out Row Num for each record and that i have done by RowNo() fuction but tha problem is that and the main requirement is that if the Id is changed then RowNo() again starts from 1,2,......

            Help Needed Sample Data File is attached

            Plz Its Urgent 

             

             

            Original Data
            IdDate
            A15/12/13 12:00 AM
            A15/13/13 12:00 AM
            A15/14/13 12:00 AM
            A15/15/13 12:00 AM
            B25/16/13 12:00 AM
            B25/17/13 12:00 AM
            C45/18/13 12:00 AM
            C45/19/13 12:00 AM
            C45/20/13 12:00 AM
            D55/21/13 12:00 AM
            E65/22/13 12:00 AM
            I want That
              Result From Above
            IdDateRow No
            A15/12/13 12:00 AM1
            A15/13/13 12:00 AM2
            A15/14/13 12:00 AM3
            A15/15/13 12:00 AM4
            B25/16/13 12:00 AM1
            B25/17/13 12:00 AM2
            C45/18/13 12:00 AM1
            C45/19/13 12:00 AM2
            C45/20/13 12:00 AM3
            D55/21/13 12:00 AM1
            E65/22/13 12:00 AM1
              • Re: Rowno() help

                Dear Arsalan,

                 

                I have answer for you. Please check this script.

                OriginalData:

                LOAD

                  Id,

                  MakeDate(Left(20&SubField(Date,'/',3),4),SubField(Date,'/',1),SubField(Date,'/',2)) as Date

                  ;

                LOAD * INLINE [

                Id, Date

                A1, 5/12/13 12:00 AM

                A1, 5/13/13 12:00 AM

                A1, 5/14/13 12:00 AM

                A1, 5/15/13 12:00 AM

                B2, 5/16/13 12:00 AM

                B2, 5/17/13 12:00 AM

                C4, 5/18/13 12:00 AM

                C4, 5/19/13 12:00 AM

                C4, 5/20/13 12:00 AM

                D5, 5/21/13 12:00 AM

                E6, 5/22/13 12:00 AM

                ];

                 

                 

                Result:

                LOAD

                  Id,

                  Date,

                  If(IsNull(Previous(Id)),1,

                  If(Id = Previous(Id) and Date > Previous(Date), Peek('RowNumber')+1,

                  If(Id <> Previous(Id) and Date > Previous(Date), 1))) as RowNumber

                Resident OriginalData;

                 

                DROP Table OriginalData;

                 

                Best Regards,

                Shenne Tannavi

              • Re: Rowno() help

                Miguel I need ur help urgent for my above post