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? 

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`

 

 

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)

 

 

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`