Equivalent of JavaScript NaN() function?

#1

I’m trying to write a transform that can handle both string and numeric values:


  start     : ~ expression "dateTime(start,'y-M-d H:m:s.SSS')"

  nValue    : ~ expression '(parseFloat(value) !== NaN ? null : value)'
  sValue    : ~ expression '(parseFloat(value) === NaN ? value : null)'
}

nValue would be set if the incoming data is numeric and sValue would be set otherwise.

The above expression seems to work as JavaScript, but doesn’t work in in the transform after provisioning.

Any ideas on how I can get the desired behavior?

I also tried variants such as isNaN(value)

but isNaN(…) function appears to be unsupported as well.

Thanks

Paul

0 Likes

#2

You should use exists() to check if the incoming value is undefined or not.
However I don’t think it is going to solve your problem. What don’t you specify your field value as anyof(double, string) ?

0 Likes

#3

Interesting suggestion. I created my entity type as follows:

entity type PiMeasurement mixes TimedDataPoint<PiMeasurement> schema name 'PI_MEASURE' {
  value : anyof(double, string)
}

…it provisions, and data loads even work too. However, when I try to fetch(…) against the type, I get an error (“unsupported value type”)

I also tried:

value : any

same problem. Am I doing something wrong still?

0 Likes

#4

do you get a response in the network?

0 Likes

#5

SOLVED! (see also another option below)

I found a solution to my problem and so thought I’d post others to help others down the road.

The core of the problem is that the Pi data (measured value) would arrive as numeric (99% of the time) or string. When it arrives as string, I can get an error in the DataLoadProcessLog such as:

errorMsg:Transformation failed: PiCompressorMeasurement.value.value Error Msg: For expression: value; expecting result type to be double but was class java.lang.String.

I originally hoped to solve this by having two value fields, nValue and sValue - tried to determine the type of the incoming value - and then storing the value the appropriate field type. But the function NaN() is not supported in the transformation. (although I think this may be possible in the latest C3 Server 7.8.1 … I haven’t tested yet).

I’m on 7.7.6 - and so my solution to this problem is to define TWO Transforms - one for string, and the other for numeric. I know that when the data comes as string, the values are:

TRUE or FALSE -> which I want to map to 1 or 0, respectively
AVAIL or UNAVAIL -> which I also want to map to 1 or 0

At the top of the transforms, I added a @condition annotation (to both) - and the condition are the inverse of each other:

@canonicalTransform(condition="value !='TRUE' && value !='FALSE' && value!='AVAIL' && value !='UNAVAIL'" )
type transform__CanonicalPiCompressorMeasurement mixes PiCompressorMeasurement transforms CanonicalPiCompressor {
...
  value     : ~ expression 'value'   // because the value is numeric, we store as-is

For the other transform:

@canonicalTransform(condition="value=='TRUE' || value=='FALSE' || value=='AVAIL' || value=='UNAVAIL'")
type transform__CanonicalPiCompressorMeasurementStringValue mixes PiCompressorMeasurement transforms CanonicalPiCompressor {
...
value     : ~ expression '(value == "TRUE" || value=="AVAIL" ? 1 : (value== "FALSE" || value=="UNAVAIL" ? 0 : value))'

The way I’ve written the logic above, I’m strictly checking for the known values so that I can become aware of any new string values arriving (e.g. if the value is not TRUE/FALSE or AVAIL/UNAVAIL, the unexpected string value will get rejected because it is non-numeric.

Last insight … Why map to numeric? Well, it was pointed out to me that even if I COULD load the value as-is as a string value, it would probably be not meaningful in later stages if I want to use it for metrics. The normalization engine requires that the value be numeric.

0 Likes

#6

SOLVED (Version 2)

In cases where you’d like to not check for explicit values, this can also be used at top of a transform to skip non-numeric values:

@canonicalTransform(condition='!matches(value, ".*[a-zA-Z].*")')
type transform__CanonicalPiCompressorMeasurement mixes PiCompressorMeasurement transforms CanonicalPiCompressor  {

...

}

make sure the !matches() expression refrences correctly the value you’re testing.

Further, be aware that anything non-numeric is ignored by this transform. You’ll need another transform to pick up non-numeric values.

1 Like

#7

SOLVED (version 3)

Me again… there are many ways to cook this… this is ultimately what I landed upon. It turns out that my data had scientific notation for very small values - and so the expression

matches(value, ".*[a-zA-Z].*")

was also catching values such as 1.23E-5 as a string, which is incorrect.

In the end, I landed on one transform with the following transformation rules which worked elegantly.

value     : ~ expression '(!matches(value, ".*[a-zA-Z].*") || matches(value, ".*[0-9]E+.*") || matches(value,".*[0-9]E-.*") ? number(value) : null)'
text      : ~ expression '(matches(value, ".*[a-zA-Z].*") && !matches(value, ".*[0-9]E+.*") && !matches(value,".*[0-9]E-.*") ? value : null)'

The above will store any numerics (including scientific notation) into value (double type) and everything else into text (string type) field.

0 Likes

#8

does it work when the value is null ? I’ve been finding in our environment that despite the ternary expression resolving to false, the number(...) expression is still throwing an NPE.

errorMsg:Transformation failed: <Type>.<field>.(exists(value) && !matchesRegex(value,'[^0-9,.]')) ? number(value) : null Error Msg: null

0 Likes