Magic ETL

Magic ETL

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?

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

Answers

  • Member

    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.

  • Contributor

    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'
  • Contributor

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

  • Wow. kudos to you.

     

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

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In