Searching over multiple columns for if statement

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.

1 Like


Here is the Ideal option.


Transform file.
IdealOptionOver40.transform (3.0 KB)


Well done @Anonymous, I didn’t think to do it that way!

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.

1 Like

@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)

1 Like

Wow. I always think im pretty good with this thing until I ask questions. Nicely done and hope to understand what you did soon!