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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 302 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 635 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 760 Beast Mode
- 64 App Studio
- 42 Variables
- 700 Automate
- 182 Apps
- 457 APIs & Domo Developer
- 51 Workflows
- 10 DomoAI
- 39 Predict
- 16 Jupyter Workspaces
- 23 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 131 Manage
- 128 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive