Integer with Value of NA??

[Former Member]

When we run a query, a column that is an integer and has no value (null) it comes back within the XML as "NA", for example: <MANUALVALUE>NA</MANUALVALUE> .

When we perform an aggregate action, the sum shows as "NA". When we assign the value to a local property, it takes a value of 0.

When we use a conditional action to check the value of the aggregate sum - nothing returns true to tell us that it has a "NA" value.

What is the proper way to compare the values of this null integer?

[Former Member · Accepted solution]

Yeah, that is kind of a pain!

I sometimes will use the calculated columns to add a column for the calculation such that if the source column is "NA", 0 is entered in the calculated column.

The other option is to repeat through the document replacing all occurrences of NA with 0.

I'm very positive there is a slicker way.

If you are querying a SQL source, perhaps you can do a CASE statement in the query to look for NULL and replace with 0.

[Former Member · Accepted solution]

Homework assignment is to see who of you can do it with the fewest actions....

[Former Member · Accepted solution]

Well, if the DB (or other data source) can return the data the way you want it, it doesn't add any actions to the transaction or process. In general, it's best to let the DBs do DB work (preaching to the choir).

Now, if the source isn't a DB, now we have a homework assignment...

Off the top of my head, I would imagine a REPEATER using xpath to filter the Column == "N/A" followed by an assignment block to replace the "N/A" with a zero for the Source.Row[#REPEATER.CURRENTITEM#] would be the least resource intensive. That's two action blocks "touching" only the needed rows.

[Former Member · Accepted solution]

Unfortunately, the [#REPEATER.CURRENTITEM#] approach won't work because the list of NA items (let's say you had 10 of them in a dataset with 100 rows) wouldn't necessarily be in positions 1...10. They could be randomly dispersed in any row #'s. Alternatively, if you had a unique field in the row, maybe a datetime value, you could key off that to do the replacement.

If all you need is stats for the non-NA values, my choice would be to apply the little-used aggregator action, with a nodelist selection something like:

YourQuery.Output{/Rowsets/Rowset/Row[YourColumn != 'NA']/YourColumn}

The aggregator will output min, max, average, count, total and standard deviation.

[Former Member · Accepted solution]

Well for our data, null is valid. We tried doing an aggregate by filtering out the NA values but it didnt work in the morning and then it did late yesterday. So we're good in that regards -- for now

But we have another problem, after the aggregate action we then take each individual value and assign it to a local property (an integer). There are some comparisons/conditionals being performed on this and unfortunately when NA is assigned to it, it is interpreted as 0. Which is different than null for our logic.

I guess what we are looking for is a nullable integer data type but that isnt there. So it looks like we just need to add a few action blocks to do some manual filtering.

[Former Member · Accepted solution]

Behind the scenes, there actually is some handling of "nillable" and "NaN" values, but, for a number of reasons, it isn't exposed at the BLS layer, unfortunately.

While not perfect, one approach you could use is to make the local properties strings instead of integers. Whenever they are used in expressions/calculations, they will be automatically converted to numbers as needed, so there's very little additional overhead, but you could store "NA" in the local property as your "null test". Again, you can freely use them in mathematical expressions as if they were numeric types.

Rick

[Former Member · Accepted solution]

Ok, you get points for a helpful answer!

I just assumed that they would be interpreted with their ASCII values and not as integers. So I thought about it but we didn't try it or even discuss it.