Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I need to calculate a counting in a simple table chart using the values of one the columns, which values are not unique.
I want a result that look like the following example
XX | Counting |
A | 1 |
A | 2 |
A | 3 |
B | 1 |
B | 2 |
C | 1 |
D | 1 |
D | 2 |
I tried to use rowno(TOTAL) but that is not the solution for my needs.
I will really appreciate any hint to solve that
Thanks in advance.
Ivan
Hi Ivan, a chart will group values based on the dimensións used, if there is only one dimension, there will be only one value per row, so I think you will need at least a second dimensión wich tells why to paint 3 different 'A'.
In script you can add this counter using a sorted load and Peek() or Previous():
LOAD XX,
If(Peek('XX')=XX, RangeSum(Peek('Counting'), 1), 1) as Counting
Resident TableName
Order By XX, OtherSortingFields;
Hi Ivan, a chart will group values based on the dimensións used, if there is only one dimension, there will be only one value per row, so I think you will need at least a second dimensión wich tells why to paint 3 different 'A'.
In script you can add this counter using a sorted load and Peek() or Previous():
LOAD XX,
If(Peek('XX')=XX, RangeSum(Peek('Counting'), 1), 1) as Counting
Resident TableName
Order By XX, OtherSortingFields;
Although not as efficient as Peek()/Previous() pointed out by rubenmarin, but AutoNumber is another possible solution for the script
LOAD XX,
AutoNumber(RowNo(), XX) as Counting
Reisdent ....
Order by XX;
Another expression using Ruben's Peek().
Check this.
Table1:
LOAD * INLINE [
XX
A
A
A
B
B
C
D
D
];
LEFT JOIN(Table1)
Table2:
LOAD *, IF(XX = Previous(XX), Peek(Counting)+1, 1) AS Counting
Resident Table1
Order By XX;