Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
Contributor
Contributor

This solution works. Thank you.

Highlighted
Contributor
Contributor

This solution works too. Thank you.

Highlighted
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;