How to create a column with an auto-increment integer value?
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.
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 thereforeberemoved.
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.
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.