Spreadsheet attached with data and spreadsheet attached with ideal result.
Essentially I would like to be able to do an if statement that looks for any cell thats over 40 hours across all columns. the column headers are weeks in a year, so you can imagine this will grow to many columns over time.
hours example.csv (529 Bytes)
The second option is not hard:
| Employee Number | 35 | 36 | 37 | |
|---|---|---|---|---|
| 111222 | 12 | 12 | 25 | none |
| 222333 | 12 | 0 | 48 | OT |
| 444555 | 12 | 0 | 27 | none |
| 555666 | 6 | 0 | 44 | OT |
| 666777 | 0 | 47.5 | 25 | OT |
over-40.transform (2.3 KB)
Not sure about the first one. I’ll think about it.
I would suggest to change one argument in the Unique transform of @Anonymous example, to catch if there are multiple weeks with overtime for a single employee.
I changed the hours for 222333 to try it.
@Olaf ,
I set that up as per the request in the data sample, also Concat unique or Concat it is same because each column represent week and there is no possibility of having same value for same employee in same week.
Here is a complete solution for the whole 52 or 53 week year.
Having column name with all the week numbers and stack it with the data files that could only have initial week numbers and might grow as the year progress, so having stacked will keep the transform same and no need to change every time when a new week is added.
Just need to make sure, that the columns that are expected in data file and the one that is chosen as Header to use with Stack they remain the same.
Transform File.
IdealOptionOver40.transform (5.1 KB)
Wow. I always think im pretty good with this thing until I ask questions. Nicely done and hope to understand what you did soon!
