Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Sept. 16, Qlik Product Portfolio Strategy and Roadmap for Data Integration 11 AM ET REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
damon_smithies
Contributor
Contributor

How to create a column with an auto-increment integer value?

Hi,

I have a very basic Table Box that is pulling data from different tables within SQL server. I am wanting to append a column to the front of the Table Box with a column that holds an integer value that increments by 1 with every row (like an auto increment identity field in SQL server).

Can any one advise how is best to go about this? I would prefer to add this as a column to the data in SQL Server but unfortunately this is not a possibility due to various limitations.

Apologies - very new to Qlik so bare with me, please try to be as descriptive as possible.

Thanks in advance.

Damon

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

You could create such a record-counter with recno() or rowno() which is counting the source- respectively the input-records, maybe in this way:

load *, rowno() as RowNo;

sql select * from YourSource;

Edit: Please be aware that this is only useful within a developing-step by checking the data-quality and something like that. Within a final application such record-id's could have a serious impact on the performance and should therefore be removed.

- Marcus

damon_smithies
Contributor
Contributor
Author

Hi marcus_sommer,

Thanks for the reply - so effectively, QlikView does not support having a column of IDs that increment for each row added? The report will be limited to 100 rows - would this affect performance as much as you suggest?

I was really hoping it would be possible - if not - I may just have to join all the data in to a view (if possible) and have an ID field on this instead.

Damon

marcus_sommer
MVP & Luminary
MVP & Luminary

If you need a record-id on the data-side then see my suggestion above. If you want to count certain field-values you could use autonumber() for it. If it should be only within a table-chart within the gui you could add a rowno() there as expression or calculated dimension.

- Marcus

johncaqc
Specialist
Specialist

If your report is limited to 100 rows go for it. Performance hit would be negligible. Use RowNo(), in a preceding load:

TEMP:

Load *,

     RowNo() as RowID;

Select *

From Your_DB;

V/r,

John