8 Replies Latest reply: Jan 19, 2017 5:17 PM by Anat Dagan RSS

    What the correct script expression to generate unique record numbers?

    Anat Dagan

      I uploaded a table of data into a table through the data load editor and I want to add a column through the script to add a unique sequential number to each row in the table. The numbers should run from 1 onward and every time new records are added via refreshing the dataload, they should also be numbered in a continuous sequence. These numbers should remain permanent.

      What's the correct script function / expression to do that?

      i tried RecNo() but it messed up my visualization.

        • Re: What the correct script expression to generate unique record numbers?
          Stefan Wühl

          Maybe Rowno()?

           

          Counters in the Load

           

          Not sure how your script looks like and how your data reload works, so this might or might not work.

           

          Please post more details about your application / load script.

            • Re: What the correct script expression to generate unique record numbers?
              Anat Dagan

              Hi Stefan,

               

              My script looks more or less like the below where i have numerous tabs with identical columns for different companies (included here 2 out of the 9 tabs i have).

               

              I built a simple table in Qliksense to show the data from all the tabs in one place and i would like to add a a unique number to each row that will not change if i sort/filter or build other visualizations based on the same source.

               

               

              [Table1]:

              LOAD

                  'company1' AS [Company],

                  [תאריך] AS [Date],

                  [שם] AS [Name],

                  [תקבולים] AS [Income],

                  [תשלומים] AS [Expense],

                  [יתרה] AS [Balance],

                  [חריגה] AS [Hariga],

                  [נפרע] AS [Paid],

                  [הערות] AS [Comments],

                  [סה"כ תקבולים / תשלומים] AS [Total Income/Expense],

                  [תקבול / תשלום],

                  [תת קטגוריה] AS [Category],

                  [שם בנק] AS [BankName]

              FROM [lib://companyxxx.xlsm]

              (ooxml, embedded labels, header is 1 lines, table is [company1]);

               

              [Table2]:

              LOAD 'company2' AS [Company],

                  [תאריך] AS [Date],

                  [שם] AS [Name],

                  [תקבולים] AS [Income],

                  [תשלומים] AS [Expense],

                  [יתרה] AS [Balance],

                  [חריגה] AS [Hariga],

                  [נפרע] AS [Paid],

                  [הערות] AS [Comments],

                  [סה"כ תקבולים / תשלומים] AS [Total Income/Expense],

                  [תקבול / תשלום],

                  [תת קטגוריה] AS [Category],

                  [שם בנק] AS [BankName]

              FROM [lib://companyxxx.xlsm]

              (ooxml, embedded labels, header is 1 lines, table is [company2]);

            • Re: What the correct script expression to generate unique record numbers?
              S Satti

              Hi Anat,

               

              Try with Iterno() Function.

               

              You will achieve your Result

               

              Regards

              Sathish

              • Re: What the correct script expression to generate unique record numbers?
                Anat Dagan

                Hi Stefan and Sathish,

                thanks for trying to help!

                 

                I concatenated all 9 excel sheets into one table on the data load editor and named  it Table1. Then at the end of the script i tried both your suggestions:

                 

                When i added the following script:

                     LOAD Iterno() as ID

                     Resident [Table1]

                I got zeros on the on the ID column table (look at left coloumn)

                 

                When i added the following script:

                LOAD RowNo() as ID

                Resident [Table1]

                 

                The table took a long time to process and the outcome was the same line multiple times with sequential ID numbers (originally this row exists only one in the data source):

                 

                Obviously I am doing something wrong but i can't figure out what.

                How can I simply create an additional column directly in the script where I assign a permanent sequential number to each row of data? like "number of records" in Tableau?

                  • Re: What the correct script expression to generate unique record numbers?
                    Wallo Atkinson

                    It's because you are joining a 1 to many in your data model.  Your straight table is showing values from both Table 1 and Table 2. 

                    If you want it only for display purposes, you could put it in a chart expression instead of the script.

                    • Re: What the correct script expression to generate unique record numbers?
                      Stefan Wühl

                      Right, you would include a new field in your table LOAD using Rowno():

                       

                      [Table1]:

                      LOAD

                         Rowno() as ID,

                          'company1' AS [Company],

                          [תאריך] AS [Date],

                          [שם] AS [Name],

                          [תקבולים] AS [Income],

                          [תשלומים] AS [Expense],

                          [יתרה] AS [Balance],

                          [חריגה] AS [Hariga],

                          [נפרע] AS [Paid],

                          [הערות] AS [Comments],

                          [סה"כ תקבולים / תשלומים] AS [Total Income/Expense],

                          [תקבול / תשלום],

                          [תת קטגוריה] AS [Category],

                          [שם בנק] AS [BankName]

                      FROM [lib://companyxxx.xlsm]

                      (ooxml, embedded labels, header is 1 lines, table is [company1]);

                       

                      Concatenate (Table1)

                      LOAD

                           Rowno() as ID,

                          'company2' AS [Company],

                          [תאריך] AS [Date],

                          [שם] AS [Name],

                          [תקבולים] AS [Income],

                          [תשלומים] AS [Expense],

                          [יתרה] AS [Balance],

                          [חריגה] AS [Hariga],

                          [נפרע] AS [Paid],

                          [הערות] AS [Comments],

                          [סה"כ תקבולים / תשלומים] AS [Total Income/Expense],

                          [תקבול / תשלום],

                          [תת קטגוריה] AS [Category],

                          [שם בנק] AS [BankName]

                      FROM [lib://companyxxx.xlsm]

                      (ooxml, embedded labels, header is 1 lines, table is [company2]);