Find Most Recent Date
Hey all, I am trying to find a way to locate the most recent date in a table based on rows of multiple columns. In other words, given a single case with variables X1, X2 and X3 I want the beastmode to find the latest date. So if column 1 was 1/1/2015, column 2 was 2/2/2014 and column 3 was 5/1/2016 then the calculation would return 5/1/2016.
Thanks all!
Best Answer
-
Oh yeah, that's a different story. Two dozen columns would be a nightmare with what I suggested.
The SQL function GREATEST() would probably work well in this case but BeastMode doesn't have that function that I'm aware.
What's your use case, just finding a max value from an entire dataset? (try using a SQL dataflow with GREATEST)
Or max value from any number of dimensional breakdowns that might be chosen with a card filter? (not sure what to do there but you'd need a BeastMode)
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Answers
-
I'd suggest doing something like max(max(x1),max(x2),max(x3)), but I don't think beastmode max functions will allow that.
Can you do a case statement with boolean?
case
when max(x1) >= max(x2) and max(x1) >= max(x3) then max(x1)
when max(x2) >= max(x1) and max(x2) >= max(x3) then max(x2)
when max(x3) >= max(x1) and max(x3) >= max(x2) then max(x3)
endSeemed to work for me when I tested it.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Yes, however I was hoping for a more elegant solution because I have about two dozen columns that I need to check against.
0 -
Oh yeah, that's a different story. Two dozen columns would be a nightmare with what I suggested.
The SQL function GREATEST() would probably work well in this case but BeastMode doesn't have that function that I'm aware.
What's your use case, just finding a max value from an entire dataset? (try using a SQL dataflow with GREATEST)
Or max value from any number of dimensional breakdowns that might be chosen with a card filter? (not sure what to do there but you'd need a BeastMode)
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Yes, I am simply trying to find the most recent date from a table. I think a SQL dataflow would work. Thanks.
0 -
For the non-SQL people like me....
Collapsing all date columns into a single column using ETL, then applying a MAX in ETL or beastmode should also do the trick
2 -
I needed to do this with 16 source columns, with the added "fun", that
case when
Col x
>Col Y
thenCol x
elseCol y
end —- had to be extended to:Case when
col x
is null thencol y
whenCol y
is null thenCol x
whenCol x
>Col Y
thenCol x
elseCol y
end…I "handled" it with a total of 15 calculated fields max_ab, max_cd… max_op, max_abcd, … max_mnop, … max_a_h, max_i_p, then finally max_a_p.
This would have been SO much easier with a GREATEST function! As long as it correctly handled null values!
0
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 88 SQL DataFlows
- 554 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 564 Beast Mode
- 9 App Studio
- 27 Variables
- 577 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 22 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 91 Manage
- 88 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive