Hi Bridger
functx:is-a-number does indeed work, but it's guts are
string(number($value)) != 'NaN'
Which seems improper somehow; it's relying on knowing the string that corresponding to the conceptual NaN result.
I may be looking for more elegance than I can plausibly expect, here. :)
Thanks! Graydon
On Sun, Feb 2, 2020 at 8:07 PM Bridger Dyson-Smith bdysonsmith@gmail.com wrote:
Hi Graydon, I'm mobile at the moment, so please excuse the abbreviated reply. Would functx:is-a-number() [#1] work in your where clause?
I'm completely unable to test... apologies.
Best, Bridger
#1 http://www.xqueryfunctions.com/xq/functx_is-a-number.html
On Sun, Feb 2, 2020, 7:22 PM Graydon Saunders graydonish@gmail.com wrote:
Hello all --
So I have a CSV file, and I can pull that into BaseX in the hopes of writing a query to extract a report. I'm using 9.3.1 for the purpose.
Not all of the Payment_Amount fields have a value, so any report-extracting query has to filter those out of any calculations or the whole thing gets infested with NaN.
This works: let $xmlReport as document-node(element(csv)) := file:read-text('report.csv') => csv:parse( map { 'header': true(), 'separator' : 'tab' })
let $made as xs:double+ := for $value in $xmlReport/csv/record/Payment_Amount[text() castable as xs:double]/number() return $value
return sum($made) => round(2)
If I wanted to use a where clause,
let $xmlReport as document-node(element(csv)) := file:read-text('report.csv') => csv:parse( map { 'header': true(), 'separator' : 'tab' })
let $made as xs:double+ := for $value in $xmlReport/csv/record/Payment_Amount/number() where ??? return $value
return sum($made) => round(2)
What do I put in the where clause? I tried where not($value = NaN) and that was not successful: "Stopped at /home/graydon/git/writing/transform/urk.xq, 6/25: [XPTY0020] element(NaN): node expected, xs:double found: 3.38."
where not($value = number('NaN'))
didn't give an error but the query returns NaN so I know I didn't filter any of the empty records from the sum.
How ought that where clause be written?
Thanks! Graydon