Use ETL to make calculated column a measure

user060210
user060210 Member
in Magic ETL

Hi there

I had a measure of DELAY expressed in words in my input file.  This cannot be changed by me.

 

I used beast mode to create a new column with the word changed to a number and I called this column DELAY2

 

However, this column is still be treated as a dimension

 

And if i open workflow to change this in ETL, the calculated column does not appear on the list of columns to be selected

 

--Any ideas how to use this of some other process to get DELAY to be a measure

Best Answer

  • Godzilla
    Godzilla Contributor
    Answer ✓

    At the end of your DELAY2 beast mode, multiply the entire thing by 1. 

    For example

     

    (case when `DELAY` = 'One' then 1 when `DELAY` = 'TWO' then 2 end) *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'

Answers

  • GrantSmith
    GrantSmith Coach

    @user060210 - It's treating it as a measure because it's not an actual number. If you do have a textual representation of a number you'll need to use the Set Column Type tile to convert it from Text to either Whole Number or Decimal. Then the card should treat it as a metric and not a dimension. As this would have to be done before the beast mode is performed on the card you'd need to translate your beastmode to some additional ETL tiles to convert the word representation to numerical representation first.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Godzilla
    Godzilla Contributor
    Answer ✓

    At the end of your DELAY2 beast mode, multiply the entire thing by 1. 

    For example

     

    (case when `DELAY` = 'One' then 1 when `DELAY` = 'TWO' then 2 end) *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'
  • user060210
    user060210 Member

    I can set entries like 'immediate' 'delayed' to be a number because it is not clear what number that would be unless I specify it. That's why I created DELAY2

  • user060210
    user060210 Member

    Thank you -- such a simple solution! Much appreciated.

Categories