topic Re: Position Within Group in QlikView

Position Within Group

lukegilligan — Thu, 26 Apr 2018 06:47:00 GMT

I am trying to capture against a subgroup within a group that resets to 1 every time the group changes.

For example:

Family Code	Name	Relationship
1	Bob	Primary
1	Jane	Partner
1	Rob	Dep Child
2	Fred	Primary
3	Amy	Primary
3	Parker	Dep Child
3	Elyse	Friend
3	Shane	Friend
3	Linkin	Friend
4	Brad	Primary
4	Shelley	Partner
5	Sam	Primary
5	Olivia	Friend

What I need is an additional field that captures if their relationship is Primary, or Friend, and increases the counter within the family every time this happens, like in the table below:

Family Code	Name	Relationship	Position
1	Bob	Primary	1
1	Jane	Partner	1
1	Rob	Dep Child	1
2	Fred	Primary	1
3	Amy	Primary	1
3	Parker	Dep Child	1
3	Elyse	Friend	2
3	Shane	Friend	3
3	Linkin	Friend	4
4	Brad	Primary	1
4	Shelley	Partner	1
5	Sam	Primary	1
5	Olivia	Friend	2

Any help would be much appreciated.

Re: Position Within Group

YoussefBelloum — Thu, 26 Apr 2018 08:50:56 GMT

Hi,

Add this line into your load:

if(Relationship='Friend',AutoNumber(RowNo(),[Family Code])+1,1) as Position

PFA

Re: Position Within Group

YoussefBelloum — Thu, 26 Apr 2018 08:54:38 GMT

And maybe stalwar1 can give you another alternative using only frond-end expression.

Tried with rangesum & above but I was stuck..

Re: Position Within Group

sunny_talwar — Thu, 26 Apr 2018 09:09:47 GMT

Something like this

=Aggr(If(Relationship = 'Friend', RangeSum(Above(TOTAL Column(1)), 1), 1), RowNum)

But I suggest this to be handled in script just like you did youssef

Re: Position Within Group

YoussefBelloum — Thu, 26 Apr 2018 09:11:53 GMT

Thanks Buddy, you rock !

here you go Luke, good luck

Re: Position Within Group

lukegilligan — Thu, 26 Apr 2018 20:40:09 GMT

Such an elegant solution that worked perfectly, thank you so much sir, you have made my day!!

Thanks again

Re: Position Within Group

YoussefBelloum — Thu, 26 Apr 2018 21:21:36 GMT

You're welcome, good luck