Is there a way to convert a numerical string to a number value in Beast Mode?

Reply
Yellow Belt

Is there a way to convert a numerical string to a number value in Beast Mode?

We have a very large dataset (10+MM rows currently) and for some reason our fiscal week is located in a column formated as YYYYMMWW.  I can use a RIGHT(`FiscalYearWeek`,2) to get the value for the week, but it's a string, not a number.  Is there a way to convert this to a number so I can then compare it via greater or lesser than?


Accepted Solutions
Brown Belt

Re: Is there a way to convert a numerical string to a number value in Beast Mode?

you can convert the string to a numeric value by multiplying it by 1. so try

 

RIGHT(`FiscalYearWeek`,2)*1

 

for me, the RIGHT function is not working i think because it's a new function that may not be rolled out on my instance. 

 

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'

All Replies
Brown Belt

Re: Is there a way to convert a numerical string to a number value in Beast Mode?

you can convert the string to a numeric value by multiplying it by 1. so try

 

RIGHT(`FiscalYearWeek`,2)*1

 

for me, the RIGHT function is not working i think because it's a new function that may not be rolled out on my instance. 

 

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Yellow Belt

Re: Is there a way to convert a numerical string to a number value in Beast Mode?

Still testing to see if it works, it seems to be just one of many issues I have in this card.  Once I can test that it fixed it's part, I'll come back and update.

Brown Belt

Re: Is there a way to convert a numerical string to a number value in Beast Mode?

Also try the following. it should work if your months are always 2 characters (01,02, 10,12, etc.)

 

SUBSTRING(`FiscalYearWeek`,7,2)*1

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Moderator

Re: Is there a way to convert a numerical string to a number value in Beast Mode?

@jlafee, tagging you to check out Godzilla's latest reply.

Highlighted
Yellow Belt

Re: Is there a way to convert a numerical string to a number value in Beast Mode?

Wow. kudos to you.

 

It solved my problem when there are character and numeric type in one field

Announcements
Call for Presenters! We want to hear your Domo story, submit your entry and win a free pass to Domopalooza 2019!. Click here!