Transforming Data before a Join

Kevin1996

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?

Find more posts tagged with

Accepted answers

ST_Superman

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`

All comments

rado98

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)