Comparing values from 3 columns, different lines

[Ricardo Araki]

Hi everyone!

 

Im creating a scorecard and I need to identify all the employees activities during the day. They will register it on system using different codes for every activity start. the table is something like this:

 

 

 

Actl_Evn_cd

Shift_Schd_Start_time

Sched_TskStart_time

Sched_Tsk_End_time

 

 

1

7:00

7:00

9:00

 

 

11

7:00

9:00

9:10

 

 

1

7:00

9:10

10:30

 

 

12

7:00

10:30

11:30

 

 

1

7:00

11:30

14:00

 

 

11

7:00

14:00

14:10

 

 

1

7:00

14:10

15:12

 

 

13

7:00

15:12

15:12

 

 

 

So Im starting with the code 1 and then I compareSched_TskStart_time toShift_Sched_start_time, if theyre equal, so its the first one. (data in bold)

Next one will be the break and here is the problem. I can also compare the 2 values, but since I have 2 breaks for each employee, I must compare it to a 4th column, Sched_tskEnd_time and see if its equal, but in the line above. (Bold Italic)

So Im trying to figure out how to indicate that it needs to compare Sched_TskStart from code 1 to Sched_TskEnd from code 11.

Im tring this, but I cannot make the second code verifcation work (bold):

case

WHEN [Actl_Evn_cd]=-999999999 and [sched_TskStart_time]=[shift_Schd_Start_time] THEN 'Start Shift'

WHEN [Actl_Evn_cd]=-999999999 and time([shift_Schd_Start_time])>Time([sched_TskStart_time]) and (([Actl_Evn_cd]=-999999999 and time([shift_Schd_Start_time]))=([Actl_Evn_cd]= 11 and Time([sched_Tsk_End_time]))) THEN 'RTN BRK 1'

ELSE ''

END

What I think is that, obviously, I cannot use these 2 conditions on same WHEN statement, but I could not figure out how to do it otherwise.

Does anybody have any idea how to do it

 

Thanks in advance!

[Gaia Paolini]

I understand you are trying to create a new column. What is its purpose and what are the expected values

 

There is no -999999999 in your data, so I don't understand your statements. Can you elaborate on the meaning of codes 1,11,12,13. I am guessing 1 is work and the rest is other activities

 

Where is the identifier of the employee in the data

 

 

[Ricardo Araki]

Hi Gaia, sorry! I changed the -999999999 in the data for '1', just to be easier to indicate, but i forgot to change it on my formula. 

And yes, Im creating a calculated column to identify the lines as Start Shift, Break1, return from break 1, lunch, return from lunch, break2, return from break 2 and end of work. 

as you can see in the example data (this stands for 1 employee day),I can easily identify the start and the end of day.

But for the return from breaks, for example,  I need to match the Sched_TskStart_time  when the Act_Evn_cd =1 with  the Sched_Tsk_End_time when the Act_Evn_cd =11.

That would be the 2 values in bold italic (9:10).

 

[Gaia Paolini]

If I understand these rules, I can think of a solution involving oneextra column (a rowID) and potentially a second extra column (the previous activity code) if you want to qualify which break the employee is coming back from.

First, define [rowID] as rowid()

Then define (optionally) [prevAct] as

Max([Actl_Evn_cd]) over (Intersect([empID],Previous([rowID])))

(Here I added a column called [empID] to label the employee. Guessing you have more than one in the dataset.)

Then define your desired column as

case

when ([Actl_Evn_cd]=1) and ([shift_Schd_Start_time]=[sched_TskStart_time]) then 'START SHIFT'

when ([Actl_Evn_cd]!=1) and ([sched_TskStart_time]=[sched_Tsk_End_time]) then 'END SHIFT'

when ([Actl_Evn_cd]=1) and ([sched_TskStart_time]=Max([sched_Tsk_End_time]) over (Intersect([empID],Previous([rowID])))) then

Concatenate('RETURN from BREAK ',[prevAct])

else Concatenate('BREAK ',[Actl_Evn_cd])

end

 

if you don't want to define [prevAct] and don't need to know which break they return from, remove the Concatenate(...,[prevAct]) from 'RETURN from BREAK'

I tried to include the definition of [prevAct] directly in the formula but for some reason it did not work.

Anyway, this should give you enough ammunition I hope.

[Ricardo Araki]

Gaia,youre awesome! It worked perfectly and this logic also helpe me with some other calculations I had to do!

Thank you very much!