What the correct script expression to generate unique record numbers?

[Anonymous]

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.

[swuehl]

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.

[Anonymous]

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]);

[satheshreddy]

Hi Anat,

Try with Iterno() Function.

You will achieve your Result

Regards

Sathish

[swuehl]

Looks like Rowno() could work with your concatenated tables, as described by Henric in his blog:

Using RowNo()

The RowNo() function is very similar to the RecNo(), but this instead counts the output records and returns the number of the current record. Also simple and useful, especially if you concatenate several input tables. In such a case, the function will return consecutive numbers for the output table.

[Anonymous]

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?

[Anonymous]

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.

[swuehl · Accepted solution]

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]);

[Anonymous]

Thanks Stefan! it worked.