Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
Add this line into your load:
if(Relationship='Friend',AutoNumber(RowNo(),[Family Code])+1,1) as Position
PFA
Hi,
Add this line into your load:
if(Relationship='Friend',AutoNumber(RowNo(),[Family Code])+1,1) as Position
PFA
And maybe stalwar1 can give you another alternative using only frond-end expression.
Tried with rangesum & above but I was stuck..
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
Thanks Buddy, you rock !
here you go Luke, good luck
Such an elegant solution that worked perfectly, thank you so much sir, you have made my day!!
Thanks again
You're welcome, good luck