Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

What the correct script expression to generate unique record numbers?

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

8 Replies
swuehl
MVP
MVP

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
Not applicable
Author

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
Creator III
Creator III

Hi Anat,

Try with Iterno() Function.

You will achieve your Result

Regards

Sathish

swuehl
MVP
MVP

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
Not applicable
Author

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
Not applicable
Author

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
MVP
MVP

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
Not applicable
Author

Thanks Stefan! it worked.