Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
singhcv123
Contributor
Contributor

autonumber for every new row

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.

3 Replies
Not applicable

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

singhcv123
Contributor
Contributor
Author

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

remi_roland
Partner - Contributor III
Partner - Contributor III

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