ETL formula vs Beastmode  result discrepancy
Good afternoon,
I've come across a headscratcher where a formula created within ETL is giving a different result when that exact same formula is created as a beastmode.
In the attached image, the columns "island break" and "test1" are the exact same formula, with the former being created in ETL and the latter as a beastmode in the card.
The duplicate formula is analyzing the hour difference between columns "max end date" and "lag min start", and if the difference in hours is to 24, the formula should return a yes, otherwise, no. You can see it is a a difference of 112 hours. So, the beastmode "test1" is correct and the etl formula "island break" is incorrect.
The "island break" ETL formula column seems to only return the wrong result when:
The Hour difference is between 100200 (it will say No instead of Yes)
or
The hour difference is between 010 hours (it will say Yes instead of No)
I am wondering if I just need to adjust something in my ETL formula..
case WHEN
((DATEDIFF(`lag min start`,`max end date`))*24)
+
(HOUR(`lag min start`)  HOUR(`max end date`))
+
((MINUTE(`lag min start`)  MINUTE(`max end date`))/60)
>= '24'
or `lag min start` is null
then 'yes' else 'no' end
Answers

@Jbrorby Since your ETL formula is incorrect, are both 'lag min start' and 'max end date' present and populated in your data for the example you showed? Also, could you post both formulas you wrote? I know sometimes I think I've written something exactly the same but I realize I had a typo or something different that I missed. And maybe post your ETL flow up to the point of your formula to see if there's anything in it that might mess something up.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
0 
Hi Rob,
I double checked to make sure the formulas match. Today, just to try something and see what happens, within the ETL formula, I removed the single quotes around '24' and ran it. Now that formula is working correctly all the way through. So that's good.
This whole thing stems from an "island and gaps" situation. It would be much more ideal to do this in SQL before getting into Domo, but its a little beyond my SQL function knowledge.
Due to a new Illinois Rest Act in our company, if an employee works 6 days in a row, they would be given at least a 24 hour break between the end of their 6th consecutive shift and the beginning of their next shift.
I think I've finally got this working in ETL with a bunch of MAX, MINS, Lags, Row numbers...yada yada, but again, would probably be much easier to accomplish in SQL if I knew how. I tried to make sense of some google examples. I kind of wish I would have used the time spent on this ETL to just learn something new in SQL
Example of how the raw data looks in SQL for 1 employee
0
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 294 Workbench
 6 Cloud Amplifier
 8 Federated
 2.8K Transform
 97 SQL DataFlows
 607 Datasets
 2.1K Magic ETL
 3.8K Visualize
 2.4K Charting
 707 Beast Mode
 49 App Studio
 39 Variables
 667 Automate
 170 Apps
 446 APIs & Domo Developer
 44 Workflows
 7 DomoAI
 33 Predict
 13 Jupyter Workspaces
 20 R & Python Tiles
 391 Distribute
 111 Domo Everywhere
 274 Scheduled Reports
 6 Software Integrations
 115 Manage
 112 Governance & Security
 Domo Community Gallery
 31 Product Releases
 9 Domo University
 5.3K Community Forums
 40 Getting Started
 30 Community Member Introductions
 103 Community Announcements
 4.8K Archive