Qlik Sense - Create ID column

[Azfar]

Hi all,

I have a table like this:

Field1
apple
16
16
orange
12
10
pineapple
34
23

How can I add ID column in the same table so it becomes like table below using load script?

Field1	ID
apple	1
16	1
16	1
orange	2
12	2
10	2
pineapple	3
34	3
23	3

Thank you.

[Vegar · Accepted solution]

If you know its every third row then you could try div()

Div( RecNo()-1,3 ) as ID

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/GeneralNu...

[Rohan · Accepted solution]

Hello,

Just a little edit :

Div( RecNo()-1,3 )+1 as ID,

otherwise the indexing starts from 0.

 or you can also use :

Ceil(RowNo()/3) as id,

Here I divided by 3 because you needed to apply the same index to 3 rows at a time. 

let me know if it works.

[Azfar]

This solution works. Thank you.

[Azfar]

This solution works too. Thank you.

[anat]

try below auto script,if u have 3 or more number of records also it will work.

test:
LOAD * Inline [
Field1
apple
16
16
17
orange
12
10
pineapple
34
23
];

Join(test)
LOAD *,RowNo() as r Resident test where IsText(Field1);

NoConcatenate
test1:
LOAD * ,if(trim(len(r))=0,Peek(r1),r) as r1 Resident test;
DROP Table test;