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
-
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`
1
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)
0 -
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`
1
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 303 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 104 SQL DataFlows
- 640 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 769 Beast Mode
- 72 App Studio
- 43 Variables
- 718 Automate
- 185 Apps
- 462 APIs & Domo Developer
- 57 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 402 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 9 Software Integrations
- 135 Manage
- 132 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive