Hi, I wonder whether someone can help me please.
I'm using number the following as part of a query to extract data from a summary Index
| stats count(eval(repayments_submit="1")) as repyaments_submit count(eval(forms_ChB="1")) as forms_ChB
The code works find, except that where the null value is null, it's shown as a zero and I'd like it to be blank.
I've tried count(eval(if(signout="1", "")))
, but I receive the following error:
Error in 'stats' command: The eval
expression for dynamic field
'eval(if(signout="1", ""))' is
invalid. Error='The arguments to the
'if' function are invalid.'
Could someone look at this please and let me know where I've gone wrong?
Many thanks and kind regards
Chris
Hi, I found the solution which is:
sum(eval(if(signout="1", "1", "")))
Many thanks to all your suggestions and help.
Kind Regards
Hi, I found the solution which is:
sum(eval(if(signout="1", "1", "")))
Many thanks to all your suggestions and help.
Kind Regards
What's the current output (with original query) and what's the expected output?
Hi @somesoni2. Thank you for coming back to me with this.
The current output is a zero, but I'd like the value to be blank please.
Many thanks and kind regards
Chris
One correction in "if" function is already mentioned by renjith.
Regarding returning a blank value: When you use count, it will always return an integer, you may have to use another eval to set the field to blank if it is "0".
Hi @cvssravan.
Thank you for this. I'll have to look to see if this is possible.
Kind Regards
Chris
@IRHM73,
if
command expects three parameters - condition, "value when match", "value when no match"
so in your case , you need to add count(eval(if(signout="1", "","<value if signout doesnt match>")))
Hi @renjith.nair.
Thank you for coming back to me with this.
Unfortunately I'd like the field to be blank if it zero rather than having a value in it. When I have tried the code you kindly provided, even putting a text value in, the field still returns a zero.
Many thanks and kind regards
Chris
Would a "-" be acceptable?
"-","")))
Hi @noy72 . Thank you for coming back to me. This is something that I may have to think about. Although not ideal, it would at least have a greater difference visually from a number.
Many thanks and kind regards
Chris