Help with streamstats count with previous sum in case of no value

[siva_cg]

Hi,

I am using streamstats to calculate the rank based on cumulative count per day per category. On few days, a particular category may not appear. So, on those days, I want to have the count for that category from the  previous day. I tried all the arguments of streamstats and other commands with no success. Can someone help me on it please?

I am pasting the code of similar case but for ranking based on the cumulative points for each football match 

index=index
| stats sum(TotalPoints) AS Points BY match, "Sold To"
| fillnull value=0
| rename "Sold To" AS Owner
| sort match
| streamstats sum(Points) AS Total BY Owner
| sort match Total
| streamstats count AS Rank BY match
| xyseries match Owner Rank

Output

match	Owner1	Owner2	Owner3
1	2 (Rank 2, Total: 10)	1 (Rank 1, Total: 15)	(Total: 0)
2	1 (Rank 1, Total: 25)	3 (Rank 3, Total: 18)	2 (Rank 2, Total: 20)
3	(Total: 0)	2 (Rank 2, Total: 23)	1 (Rank 1, Total: 30)

Expected Output

match	Owner1	Owner2	Owner3
1	2 (Rank 2, Total: 10)	1 (Rank 1, Total: 15)	3 (Rank 3, Total: 0)
2	1 (Rank 1, Total: 25)	3 (Rank 3, Total: 18)	2 (Rank 2, Total: 20)
3	2 (Rank 3, Total: 25)	3 (Rank 2, Total: 23)	1 (Rank 1, Total: 30)

[ITWhisperer · Accepted solution]

Your first stats should be a chart command, because stats doesn't give you the null entries you are expecting. You then need to untable the chart after the fillnull to get the stats you were expecting

index=index
| chart sum(TotalPoints) AS Points BY match, "Sold To"
| fillnull value=0
| untable match Owner Points
| sort match
| streamstats sum(Points) AS Total BY Owner
| sort match Total
| streamstats count AS Rank BY match
| xyseries match Owner Rank

[siva_cg]

Thank you. It worked as expected

[PickleRick]

You can either modify the intermediate results.

[...]
| streamstats count AS Rank by match
| streamstats current=f window=1 last(Rank) AS prevrank by match
| eval Rank=if(Rank=0,prevrank,Rank)
| xyseries [...]

Or you can modify the final table. But it needs a "matchable" set of fields since you can't match negatively with foreach and you can't (at least I don't know of any way to do so) check field's name in eval.

[...]
| foreach Owner*
   [ <<FIELD>>=if(<<FIELD>> > 0, <<FIELD>>, null()) ]
| filldown Owner*
| fillnull value=0 Owner*

But I'd go with the first one (the streamstats)