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