5 Replies Latest reply: Feb 11, 2016 5:34 AM by Avinash R RSS

    Rate of Pay

    Mick Hancock

      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;

        • Re: Rate of Pay
          Avinash R

          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";

            • Re: Rate of Pay
              Mick Hancock

              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.

            • Re: Rate of Pay
              Jonathan Dienst

              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.

                • Re: Rate of Pay
                  Mick Hancock

                  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.