ETL - Create Column with Max Date
I am creating a dashboard using the Illustrator plugin so Beast Mode calculations will not work unfortunately. I am looking to show the date of the newest data on the dashboard. I have a column called "Date" so I need to pull the max date and stamp it to a new column.
Is that possible or will I have to use a SQL dataflow?
Thanks!
Comments
-
I'm also looking to do this in Magic ETL. Any resolution?
0 -
You will need to add it as a column to your data set - you can do this in Magic ETL or in your original data set update.
First, if you can change the data set you are bringing in to add a "Current Date" or a "Max Date"in our SQL statement, that is probably the easiest way. IF you do not have access to change the code that creates the data set then Magic ETL is the way to do it.
The ETL looks like this:
If you have a column in your table that is constant for all entries, you can kip the Add Constants part. For Add Constants I add a column I call "Constant" and put in the value of 1 (this could be anything you want).
Now group data by the constant and the new field is "Latest Date" and select the column the Date is in and select Maximum in the aggregation method field. something like this:
Now combine the group by and the Constant boxes with a join and the "Constant Field" is the common link. Have all fields go to the new outfile and then you will have an outfile to use in your cards / illustrator.
Dojo Community Member
** Please like responses by clicking on the thumbs up
** Please Accept / check the answer that solved your problem / answered your question.2 -
This is an excelent solution, thanks for posting!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive