3 Replies Latest reply: Nov 16, 2016 5:54 AM by R�mi ROLAND RSS

    autonumber for every new row

    Virendra Chawra

      Hi ,

       

      I am doing a developement of the script , where i want to generate a autonumber id for every row.

       

      but the logic is for example...currently i have loaded 10 rows and the autonumber should be 1-10.

       

      but tomorrow when i will load the new file the data should append and new rows should start from 11 and more.

       

      Like:

       

      current load ID should be autonumber.

       

      ID    Name

      1     x

      2     y

      3    z

       

      when i will get data new data than our rows would like

      ID    Name

      1      x

      2      y

      3      z

      4     a

      5     b

      6     c

       

       

      please suggest ...how to append new rows with progressive autonumber ids.

        • Re: autonumber for every new row
          Srashti Srivastava

          Hi Virendra,

           

          Hope the attached solution solves your problem.

           

          Tbl1:

          LOAD Name,

               RecNo() as RecNo

          FROM

          [239502QC Thread 239502.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          LOAD

          AutoNumber(RecNo&Name) as ID,

          Name

          Resident

          Tbl1;

           

           

          DROP Table Tbl1;

          Regards,

          Srashti

          • Re: autonumber for every new row
            Virendra Chawra

            That is fine but what when i will do the incremental load.

             

            let's assume we have 1--5 id generated with your load statement.

             

            now i want to load new data but data should append from row 6 ---n with proper id ....

              • Re: autonumber for every new row
                R�mi ROLAND

                Hi,

                 

                Autonumber will only work fine inside your script execution, next time you'll execute your script Qlikview will not have any clue of what value is behind autonumbered column.

                Best option for that kind of case is using autonumberHash function that function wil generate a longer Hash sting based on value of original(s) column.

                If you are looking for better performances I suggest to don't use it, the best option will be to store the original ID column every day and recalcul autonumber after adding increment data.

                 

                Like this:

                LET var.today = date(today(),'YYYY-MM-DD');

                 

                Tbl1:

                LOAD

                     $(var.today) as Date,

                     Name,

                     RecNo() as RecNo

                FROM

                [239502QC Thread 239502.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                STORE Tbl1 INTO tbl1_$(var.today).qvd (qvd);

                DROP Table Tbl1;

                 

                LOAD

                AutoNumber(Date&RecNo&Name) as ID,

                Name

                FROM tbl1_*.qvd (qvd);