New Topic

Community Topic Question Data Transformation

Category Data Transformation

Product Version 8.0

Version 8.0.2, Convert dimension to metric (measure)

Roar Iversen shared this question ago

Answered

Hi,

I have a var char column in the view containing data (1, 2, 3, 4, 5, N/A) from a survey.

I want to display the numerical data of the column in a chart using the Meter chart. It requires a measure.

How do I convert the data to measure (numeric) to enable the Meter chart?

I've tried the Dimension/Convert/Text to numeric function, but that doesn't do the trick.

The same question

Replies (1)

Mike Sheehan ●

Hi Roar,

Thanks for reaching out. I'm not entirely sure the following will work in 8.0.2, but it does in 9.0.4.1.

You can create a Calculated Field with a CASE Statement like follows:

/b82d91beb307868bfa64754d0ce7e1f2

This is my original column and the Calculated Field:

/e6635de722cff419961cb2f647719bff

So you can see it's now a Metric field, and the non-numeric value is now a null value.

Please give this a try and let me know how goes. If this doesn't work, please let me know what error message you see in the UI and/or in the logs when you try to validate or save it.

Regards,

Mike

Reply URL

Roar Iversen ●

Hi,

Thank you for responding. My dialog looks different from yours. I don't have the option to set resulting field type. It is possible to set when using Formula Type 'Freehand SQL', but then it reverts back to Dimension every time I try to set it to Metric.

bc206c8d2449c4e8cd81a4ec5e293710

The result looks like this with the output as Dimension.

3c32841e552bd9f3eaa0312a6c750bae

I've using the 'Text to Numeric' converter, but than doesn't help either as I do it.

regards Roar

URL

Roar Iversen ●

Hi,

After some testing I was able to use the Freehand SQL option to convert to numeric (metric). Thanks for the input as it triggered my solution.

regards Roar

/803d11ad175d9f281a752256da138f55

URL

Mike Sheehan ●

Hi Roar,

Glad to hear it! I probably should have mentioned in my reply that in order to get the field recognized as Metric in 8.0.2 in this context, you may need to write the CASE statement in a Freehand SQL Formula instead, so, nice one!

Considering this is now sorted, I'll go ahead and close this ticket out, but please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike

URL