Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to create pairs of values in data. I have a fied ID and i want to know how to get field Pair:
ID,Pair
a,1
a,1
a,2
a,2
a,3
b,1
c,1
c,1
d,1
d,1
d,2
e,1
e,1
e,2
e,2
e,3
e,3
e,4
f,1
f,1
f,2
.
.
That is, the 2 first occurences of a value with 1, the 3rd & 4rd, 2, and so on.
It has to be done in script so that I get the table above
Thanks a lot in advance
Best I have for you is a link to the Design Blog area to see if you can find something there that may help:
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
There are hundreds of posts on things there, so you may have to try different things to find something that may help here if you are still working on things. Sorry I do not have something better for you.
Regards,
Brett
Try this
Temp:
load * , if( Peek(ID)<>ID ,1,peek(rows)+1) as rows , RowNo() as row inline [
ID
a
a
a
a
a
b
c
c
d
d
d
e
e
e
e
e
e
e
f
f
f
];
load
ID,
rows,
row,
if(row=1 or Peek(ID)<>ID ,1 ,
if( Peek(ID)=ID and odd(rows)='-1', Peek(new)+1 , Peek(new) )) as new
Resident Temp
Order by row
;