Transforming Data before a Join

Hello,

I've been trying out DOMO for a week now, and it seems to work a bit different than other BI tools. What I want to do, is add a calculated column to a table, based on a formula (in this case, based on DATEDIFF since I want to calculate Age from Date of Birth). After this, I want to add a conditional column Age Group, that organizes the different Ages into their corresponding Age Groups. 

 

Now I know I can accomplish this easily by a few beastmodes, but the problem is that (if I'm right) this only creates virtual columns to use directly in charts. I want to add the columns mentioned above, and then join another table based on the Age Group values. 

I already looked at Magic ETL but it doesn't seem to be possible there. Would this be possible by using SQL? Or is this even possible at all within DOMO? 

Best Answer

  • ST_-Superman-_
    Accepted Answer

    This is definitely something that can be handled in a MySQL dataflow.

     

    Select

    *,

    floor(datediff(curdate(),`Date_of_Birth`)/365) as `Age`

    from data_table

     

    then you can use the `Age` field to create your `Age Bucket` Field:

     

    select

    *,

    case when `Age`<18 then 'Minor'

    when `Age`<30 then 'Young Adult'

    when `Age`<50 then 'Middle Age'

    ... etc.

    end as `Age Bucket`

     

     

Answers

  • You  can do a seudo Datediff in Magic ETL

     

    First Convert the date columns into decimal.

    Then subtract.

    Then convert to Days (multiply by 1 440 000).

     

    When Dates are converted to decimal 1 equals 1 miliseconds (and for reference 0 is 1 Jan 1970)