Count the number of occurrences above (string)

[FelipeB]

Greetings, everyone.

Basically I need to count the number of accurrences of the second column (ConcatNCM) from that line above. The first column (NúmerodaLinha) represents the row number from database.

I'm lost . I've already tried 

=RangeCount(Above(Count(ConcatNCM),0,RowNo()))

=RangeCount(Above(Count(ConcatNCM),0,NúmerodaLinha()))

The answer on the fourth column should be 1,2,3,4 and 5 respectively.

 

[pedrohenriqueperna]

Boa tarde, apanhei com essa ai também. Ai pensei em usar o "modifier" padrão da tabela utilizando Count(ConcatNCM), definindo o modificador como "accumulation" e o "range" full.

Se preferir, segue a expressão gerada automatica, mas é bem bagunçada:

Sum({1<[$(=Replace(GetObjectField(0),']',']]'))]={">=$(=Min([$(=Replace(GetObjectField(0),']',']]'))]))<=$(=Max([$(=Replace(GetObjectField(0),']',']]'))]))"},[$(=Replace(GetObjectField(1),']',']]'))]={"=Only({1}[$(=Replace(GetObjectField(1),']',']]'))])>='$(=Replace(Replace(MinString([$(=Replace(GetObjectField(1),']',']]'))]),'''',''''''),'$','$''&'''))' and Only({1}[$(=Replace(GetObjectField(1),']',']]'))])<='$(=Replace(Replace(MaxString([$(=Replace(GetObjectField(1),']',']]'))]),'''',''''''),'$','$''&'''))'"}>}Aggr(RangeSum(Above(If(Count([$(=Replace(GetObjectField(0),']',']]'))]) * Count([$(=Replace(GetObjectField(1),']',']]'))]) > 0, (　Count(ConcatNCM)　) + Sum({1<[$(=Replace(GetObjectField(0),']',']]'))]={">=$(=Min([$(=Replace(GetObjectField(0),']',']]'))]))<=$(=Max([$(=Replace(GetObjectField(0),']',']]'))]))"},[$(=Replace(GetObjectField(1),']',']]'))]={"=Only({1}[$(=Replace(GetObjectField(1),']',']]'))])>='$(=Replace(Replace(MinString([$(=Replace(GetObjectField(1),']',']]'))]),'''',''''''),'$','$''&'''))' and Only({1}[$(=Replace(GetObjectField(1),']',']]'))])<='$(=Replace(Replace(MaxString([$(=Replace(GetObjectField(1),']',']]'))]),'''',''''''),'$','$''&'''))'"}>}0), 0), 0, RowNo())), ([$(=Replace(GetObjectField(1),']',']]'))], (Numeric, Ascending), (Text, Ascending)), ([$(=Replace(GetObjectField(0),']',']]'))], (Numeric, Ascending), (Text, Ascending))))

[Qrishna]

A simple one liner code using load editor script. No need of any complex front end expressions.

TABLE_TEMP:
load *,
AutoNumber(ConcatNCM&'-'&Numer,ConcatNCM) as runningcount
inline [
Numer, ConcatNCM
1001,AAA
2,BBB
3,CCC
4,DDD
1002,AAA
6,CCC
7,CCC
8,EEE
9,BBB
10,CCC
11,CCC
12,EEE
1003,AAA
14,BBB
15,EEE
16,BBB
1004,AAA
18,AAA
19,CCC
20,BBB
];

[Kushal_Chawda · Accepted solution]

@FelipeB  try below expression

sum(aggr(rangesum(above(Count(NúmerodaLinha),0,RowNo())),ConcatNCM, NúmerodaLinha))

[FelipeB]

Thank you, @Kushal_Chawda .

How do I convert it into a SQL formula on LOAD Section?

Unfortunately I realized that this formula may not work as intended when user apply filters.

Therefore I need to create pre-calculated collumn with the same formula.