Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

singhcv123
New Contributor II

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

Re: autonumber for every new row

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
New Contributor II

Re: autonumber for every new row

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

Partner
Partner

Re: autonumber for every new row

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