Rate of Pay

[#1]

Hi,

I am very much a beginner when it comes to QlikView. I have been asked to create a Dashboard on how many hours people work per week/fortnight. But with a slight variation - for example Bob worked 10 hours today. I need to break down that 10 hours into payment type. PAYMENT_TYPE for example consists of (SIT - Site Allowance, BAS - Base Rate, OTD - Overtime Double, OTN - Overtime Normal, MEA - Meal Allowance and so on.

I'm not sure how to go about this. If someone could please help that would be great. Thanks

Example

Monday               7.6 BAS                 1.2 OTN                .9 OTD                   Total Hours Paid = 9.7hours

Tuesday               7.6 ANN               7.6 LOA                                                 Total Hours Paid = 7.6 hours

Wednesday        7.6 BAS                 2 OTN                    .4 OTD                   Total Hours paid = 10 hours        

PayMaster:
LOAD
"EMPLOYEE_CODE",
"COST_CENTRE_CODE",
"EMPLOYEE_NAME",
"PAYROLL_CODE",
"POSTAL_ADDRESS_1",
"POSTAL_ADDRESS_2",
"POSTAL_ADDRESS_3",
"BIRTH_DATE",
"PAY_RATE",
"LAST_PAY_VALUE",
"SEX",
"MOBILE_NBR",
"ORIGINAL_START_DATE",
"YEARS_WORKED",
"YTD_NET_VALUE";

SQL SELECT
"EMPLOYEE_CODE",
"COST_CENTRE_CODE",
"EMPLOYEE_NAME",
"PAYROLL_CODE",
"POSTAL_ADDRESS_1",
"POSTAL_ADDRESS_2",
"POSTAL_ADDRESS_3",
"BIRTH_DATE",
"PAY_RATE",
"LAST_PAY_VALUE",
"SEX",
"MOBILE_NBR",
"ORIGINAL_START_DATE",
"YEARS_WORKED",
"YTD_NET_VALUE"
FROM "T_CATALANO".dbo."PAY_MASTER";

//-------- End Multiple Select Statements ------


RawTimeSheets:
LOAD
EmployeeID,
DayDate,
NetHours,
PayCodeID,
CostCentreId;
SQL SELECT *
FROM "IMIS_CATALANO".dbo.RawTimesheets;


//-------- Start Multiple Select Statements ------

PayTrans:
LOAD "EMPLOYEE_CODE",
"PAY_HOURS",
"TRANS_DATE",
"LINE_TYPE",
"PAYMENT_TYPE",
"NET_VALUE",
"QUANTITY";
SQL SELECT "EMPLOYEE_CODE",
"PAY_HOURS",
"TRANS_DATE",
"LINE_TYPE",
"PAYMENT_TYPE",
"NET_VALUE",
"QUANTITY"
FROM "T_CATALANO".dbo."PAY_TRANS";


//-------- Start Multiple Select Statements ------



//-------- Start Multiple Select Statements ------
LOAD "COST_CENTRE_CODE",
"COST_SALES_GL_ACCT";
SQL SELECT "COST_CENTRE_CODE",
"COST_SALES_GL_ACCT"
FROM "T_CATALANO".dbo."JOB_CCTR_NAMES"
where "COST_CENTRE_CODE" like 'lab%';
//-------- End Multiple Select Statements ------


//-------- Start Multiple Select Statements ------
LOAD "ACCOUNT_NAME",
"GENERAL_ACCOUNT" as  "COST_SALES_GL_ACCT";
SQL SELECT "ACCOUNT_NAME",
"GENERAL_ACCOUNT"
FROM "T_CATALANO".dbo."GEN_ACCT_NAMES";
//-------- End Multiple Select Statements ------


//-------- Start Multiple Select Statements ------
Employees:
LOAD "AwardID",
// "EmployeeName" as "EMPLOYEE_NAME",
   "EmployeeCode" as "EMPLOYEE_CODE",
"ID" as "EmployeeID",
"ProjectAwardId";
SQL SELECT "AwardID",
"EmployeeName",
"ProjectAwardId",
"ID",
"EmployeeCode"
FROM "IMIS_CATALANO".dbo."Employees";


//-------- Start Multiple Select Statements ------

Awards:
LOAD "ID" as "AwardID",
"AwardDescription",
"AwardCode";
SQL SELECT "ID",
"AwardDescription",
"AwardCode"
FROM "IMIS_CATALANO".dbo.Awards;

[avinashelite]

Identify the data points where it is stored like the Timesheet filled against each task....billing against each etc ..

as per your inputs I think your data might be residing in this two table , then add the employee as the dimension and add expression hour filled against each task , finally sum up all the task ..

Hope this helps you

RawTimeSheets:

LOAD
EmployeeID,
DayDate,
NetHours,
PayCodeID,
CostCentreId;
SQL SELECT *
FROM "IMIS_CATALANO".dbo.RawTimesheets;


//-------- Start Multiple Select Statements ------

PayTrans:
LOAD "EMPLOYEE_CODE",
"PAY_HOURS",
"TRANS_DATE",
"LINE_TYPE",
"PAYMENT_TYPE",
"NET_VALUE",
"QUANTITY";
SQL SELECT "EMPLOYEE_CODE",
"PAY_HOURS",
"TRANS_DATE",
"LINE_TYPE",
"PAYMENT_TYPE",
"NET_VALUE",
"QUANTITY"
FROM "T_CATALANO".dbo."PAY_TRANS";

[jonathandienst]

I think you have all the data you need for your analysis - you also have some that you may never use (eg telephone numbers and employee addresses).

One other thing you might need is a lookup table for the payment types (PAYMENT_TYPE?) that identifies whether the hours are work or non-work (eg ANN not counted in work hours?). Do this with a mapping table like this at the start of your script:

     MapPaymentTypes:

     Mapping LOAD * Inline

     [

          PAYMENT_TYPE, IsWork

          ANN, 0

          BAS, 1

          SIT, 1

          OTD, 1

          MEA, 0

         ...

     ];

Now when you load PayTrans:

PayTrans:

LOAD EMPLOYEE_CODE,

  PAY_HOURS,

  TRANS_DATE,

  LINE_TYPE,

  PAYMENT_TYPE,

  ApplyMap('MapPaymentTypes', PAYMENT_TYPE, 0) As IsWork,

  NET_VALUE,

  QUANTITY;

SQL SELECT EMPLOYEE_CODE,

  PAY_HOURS,

  ...

You could also load the cost centre / account codes and names using ApplyMap into the PayMaster table to reduce the number of tables.

Remove the fields that you do not need.

Add a master calendar, or at least derive some calendar fields from the relevant date (PAY_DATE or DayDate?). Search this site for information on master calendars.

Finally, the link between EmployeeID and EMPLOYEE_CODE is in the IMIS_CATALANO.dbo.Employees table. Make sure all the EMPLOYEE_CODEs and EmployeeIDs are covered by this table, otherwise you will have some EmployeeID records that are not associated with any EMPLOYEE_CODE related data.

[#4 · Accepted solution]

Thankyou so much for your quick response. After further investigation there was a table I was missing (Generated Allowances). Everything I needed was in that table - Description, DayNo, and Quantity. The QUANTITY I was using was in the PAY_TRANS Table and I needed the Quantity in the Generated Allowance Table. I then used aggr(sum(Quantity),Description,DayNo) as the Expression.

[#5]

Thankyou very much for your quick response. After further investigation there was a table I was missing (Generated Allowances). Everything I needed was in that table - Description, DayNo, and Quantity. The QUANTITY I was using was in the PAY_TRANS Table and I needed the Quantity in the Generated Allowance Table. I then used aggr(sum(Quantity),Description,DayNo) as the Expression. The information you sent me was very helpful for another QlikView Dashboard I'm doing at the moment.

[avinashelite]

if you have got the answer please mark the correct and helpful answers and close this thread