0 Replies Latest reply: Jul 21, 2016 9:11 AM by Michael Taylor RSS

    Data grouping and other

    Michael Taylor

      Been using Qlikview and got that working.

      here is my load script in qlikview.

       

      LOAD [Employee Name],

           Company,

           Shift,

           strClassName,

           PayrateID,

           OvertimeDate,

           Processes,

           OvertimeWorked1 as overtimeworked,

           OvertimeRate1 as overtimerate,

           1 as type,

           OvertimeWorked1*OvertimeRate1*PayrateID as OvertimeCosts,

           OvertimeWorked2,

           OvertimeRate2,

           ToilTaken,

           HoursCashed,

           TotalCost,

           MonthN,

           YearName ([OvertimeDate]) as year,

           MonthName ([OvertimeDate]) as Month,

           QuarterName ([OvertimeDate]) as Quarter,

           WeekName ([OvertimeDate]) as Week

               

      FROM

      [H:\Archived\Overtime Tracker\Overtime Database\qry_AllData.xlsx]

      (ooxml, embedded labels, table is qry_AllData);

      Concatenate Load [Employee Name],

           OvertimeDate,

           PayrateID,

          OvertimeWorked2 as overtimeworked,

          OvertimeRate2 as overtimerate,

          OvertimeWorked2*OvertimeRate2*PayrateID as OvertimeCosts,

          2 as type

      FROM

      [H:\Archived\Overtime Tracker\Overtime Database\qry_AllData.xlsx]

      (ooxml, embedded labels, table is qry_AllData);

       

       

      I am trying to acheive the same thing through Cliksense.

      I have connected to the database qry_AllData through the data connect manager and get all the fields within the database fine, but I want to include the

           YearName ([OvertimeDate]) as year,

           MonthName ([OvertimeDate]) as Month,

           QuarterName ([OvertimeDate]) as Quarter,

           WeekName ([OvertimeDate]) as Week

      and

      Concatenate Load [Employee Name],

           OvertimeDate,

           PayrateID,

          OvertimeWorked2 as overtimeworked,

          OvertimeRate2 as overtimerate,

          OvertimeWorked2*OvertimeRate2*PayrateID as OvertimeCosts,

          2 as type

      sections

       

      just struggling on how to apply these statements when the database is already loaded in the Auto-generated section

       

      My autogenerated section code is:

      LIB CONNECT TO [H--Archived-Overtime Tracker-Overtime Database-Overtime Database.accdb];

       

       

      [qry_AllData]:

      SQL SELECT `Company`,

        `Employee Name`,

        `EmployeeID`,

        `HoursCashed`,

        `Month`,

        `NonProcessSubTaskID`,

        `Overtime1Costs`,

        `Overtime2Costs`,

        `OvertimeDate`,

        `OvertimeRate1`,

        `OvertimeRate2`,

        `OvertimeWorked`,

        `OvertimeWorked1`,

        `OvertimeWorked2`,

        `PayrateID`,

        `Processes`,

        `ProcessID`,

        `Quarter`,

        `Shift`,

        `strClassName`,

        `ToilTaken`,

        `TotalCost`,

        `week`,

        `Year`

      FROM `qry_AllData`;

       

      Looked around and just can't see any tips showing something similar to my issue.

       

      Any help or pointers greatly appreciated.