Actual Hours Worked versus Hours Available

[#1]

I'm trying to create expressions that will show "% Hours entered yesterday" among other visualizations and calculations.

I have a table OPS_CF which lists every mechanic and the hours they can work in a day (in this case most are 8). This field is STD_HOURS_AVAILABLE and represents the total hours of standard time a mechanic can work on any given day.

I have a second table OPS_WRCF which lists mechanics and the hours they worked on any given work order (i.e. four mechanics can work on one work order over four days, so one mechanic worked 8 hours a day on 6/1-6/3 he will have 3 records for a work order.)

Showing "% Hours entered Yesterday" would be calculating the actual hours put in from OPS_WRCF for yesterday and showing what percentage that is in comparison to the total hours available from the OPS_CF table for that day and that mechanic.

Is this possible? Would it be better to create a table with all the data in it? How would I write this in an expression, or would it be better to script?

Further explanation: John Smith is available to work 8 hours a day. He works on Work Order #1 for three days, and works 8 hours on Day 1, 6 hours on Day 2, and 8 hours on Day 3. His total actual hours worked is 22, but he was available for 24. I am trying to show all actual hours a mechanic put in for a specific day in comparison to what hours all of those mechanics were actually available on a specific day.

Thanks!

[gsbeaton]

Hi Casey,

The script will still work and not double count.  The OPS_WCRF table should not contain the STD_HOURS_AVAIL column.  Rather, it should only contain the PercentTimeWorked  field.  So, OPS_WRCF will look like this:

Date	Mechanic	WRD_ID	HoursRecorded	PercentTimeWorked
6/2/17	Mech1	WR_12	2.5	0.25
6/2/17	Mech1	WR_11	2.5	0.25
6/2/17	Mech1	WR_09	3	0.3

Your OPS_CF table will continue to look like this:

Mechanic	STD_HOURS_AVAIL
Mech1	10
Mech2	8

When you reload the application, the QIX engine associates the two tables on the shared field Mechanic.  The key point here is that it associates, rather than joins.  So when you do SUM(STD_HOURS_AVAIL) (and presuming you have selected Mech1 somewhere), the result will be 10, and not 30.

[gsbeaton]

No, it's because I wrote psuedo code

Map_MechToHours: 
Mapping Load 
MechanicID 
,HoursAvailable 
From OPS_CF; 

The From is wrong.  This would have to refer to your actual data.  I'm not sure how you're connecting, but it would need to be something like

Map_MechToHours:  
Mapping Load   
 MechanicID  
,HoursAvailable  
Resident OPS_CF;  

or

Map_MechToHours:  
Mapping Load   
 MechanicID  
,HoursAvailable  
From (qvd);  

Does that help?

[#4]

Ah okay. I made the necessary changed but it just created a new table called Map_MechToHours with everything from WRCF in it, plus PercentHoursWorked. It also generated a massive synethic key. Did I do something wrong?

[gsbeaton]

Yes, it sounds like it.  Mapping tables are automatically dropped before Sense does the associations.  It sounds like it stayed in memory for some reason.  Did you definitely use the Mapping keyword?

[#6]

That I did, I modified the script to include the longer table names and it reads as:

Map_CFtoHoursAvailable: 

  Mapping Load  

  CF_ID, 

  V_QLIK_OPS_CF.STD_HOURS_AVAIL

  Resident V_QLIK_OPS_CF; 

Load * 

  ,[HOURS_STRAIGHT]/ApplyMap('Map_CFtoHoursAvailable', CF_ID) as PercentTimeRecorded 

Resident V_QLIK_OPS_WRCF; 

Is it a preceding load issue? I just created a new section of script and called it "Mapping" and put it as the last section in the data load editor.

[gsbeaton]

I think the problem is with your second load there.  Sense is creating an identical table to V_QLIK_OPS_WRCF with the addition of the PercentTimeLoaded column then joining on every matching column.

When you load in V_QLIK_OPS_WRCF the first time, call it something like T1.  Then, modify your code like this:

Map_CFtoHoursAvailable: 
  Mapping Load  
  CF_ID, 
  V_QLIK_OPS_CF.STD_HOURS_AVAIL
  Resident V_QLIK_OPS_CF; 

V_QLIK_OPS_WRCF:
Load * 
  ,[HOURS_STRAIGHT]/ApplyMap('Map_CFtoHoursAvailable', CF_ID) as PercentTimeRecorded 
Resident T1; 

Drop table T1;

That should do it.

[#8]

I'll give it a shot. I do have final question: How would this interpret negative hours? Sometimes a mechanic entered in 4 hours and meant to input 2 so for the same Work Request he will put in -2 hours for straight time.

[gsbeaton]

This would work for negative hours too as the equation would give a negative percentage, so

0.25

0.25

0.25

-0.25

3

Would still sum to 8 for the mechanic's time for that day.

At the risk of complicating things, you could speed up your script a little by putting the

[HOURS_STRAIGHT]/ApplyMap('Map_CFtoHoursAvailable', CF_ID) as PercentTimeRecorded 

line straight into the first and main load of your data.  As long as you have your mapping load before you call the applymap, this would work, and mean you don't have to do a second load from resident, followed by the drop table.

Either way will work though.

George