Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Qlik Sense - Create ID column

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?

Field1ID
apple1
161
161
orange2
122
102
pineapple3
343
233

 

Thank you.

2 Solutions

Accepted Solutions
Partner
Partner

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Creator
Creator

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.

View solution in original post

5 Replies
Partner
Partner

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Creator
Creator

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.

View solution in original post

Contributor
Contributor

This solution works. Thank you.

Contributor
Contributor

This solution works too. Thank you.

Creator
Creator

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;