topic Re: How to create a column with an auto-increment integer value? in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

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

marcus_sommer — Wed, 03 Jan 2018 16:37:37 GMT

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

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

Anonymous — Wed, 03 Jan 2018 16:53:41 GMT

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

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

marcus_sommer — Wed, 03 Jan 2018 17:01:30 GMT

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

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

johnca — Wed, 03 Jan 2018 17:07:52 GMT

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