2 Replies Latest reply: Oct 29, 2012 6:34 PM by Don Green RSS

    How to access fields in a file created higher up in my script

    Don Green

      Hi everyone

       

      Another newbie post. I am creating a Staff (FieldStaff) table, with a CommencementDate from a SQL query.

       

      I then want to load data from a QVD file, and pick up hours and shifts data from there.

       

      I can join on FieldDtaffNo to calculate total hours worked.

       

      How can I filer on a field from my FieldStaff table, to calculate figures to load from the QVD file (see commenteed area below).

       

      Basically I want to use CommencementDate from my created table in a calculation, or as a filter on my QVD file.

       

      ShiftDate - CommencementDate AS DaysToFirstWorked

       

      ShiftDate is from the QVD file, CommencementDate is from the FieldStaff: table.

       

      If I use CommencementDate, or FieldStaff.Commencement date, it still gives me an unknown column error.

       

      I also want to use the CommencementDate column on my FieldStaff table as a base date to calculate TotalHoursWorked in the first 30 days from CommencementDate etc.

       

      Any hints for a newbie?

       

       

      FieldStaff:

      LOAD     employeenumber as [FieldStaffNoJoin],

          surname as [Surname],

          CommencementDate,

      SQL Select    e.employeenumber,

              e.surname,

              e.commencementdate as CommencementDate;

       

      TotalHoursWorkedTable:

      LOAD

              FieldStaffNo,

              SUM(ShiftLength) AS TotalHrsWkd,

              COUNT(ShiftID) AS TotalShiftsWkd,

              MIN(ShiftDate) AS FirstShiftWkd,

              MAX(ShiftDate) AS LastWkd

      FROM

      [D:\QV Development\ShiftsData.qvd] (qvd)

      WHERE [IncludeTransaction] = 'Yes'

      GROUP BY FieldStaffNo;

       

      TotalHoursWorked:

      Left Join     (FieldStaff)

      LOAD         

                  FieldStaffNo,

                  TotalHrsWkd AS TotalHoursWorked,

                  TotalShiftsWkd AS TotalShiftsWorked,

                  FirstShiftWkd AS FirstShiftWorked,

                  LastWkd AS LastWorked

      Resident    TotalHoursWorkedTable;

      Drop Table     TotalHoursWorkedTable;

       

       

      //DaysToFirstShiftTable:

      //LOAD

      //        FieldStaffNo,

      //        ShiftDate - CommencementDate AS DaysToFirst

      //FROM

      //[D:\QV Development\ShiftsData.qvd] (qvd)

      //WHERE [IncludeTransaction] = 'Yes';

      //

      //DaysToFirstShift:

      //Left Join     (FieldStaff)

      //LOAD         

      //            FieldStaffNo,

      //            DaysToFirst AS DaysToFirstShift

      //Resident    DaysToFirstShiftTable;

      //Drop Table     DaysToFirstShiftTable;

        • Re: How to access fields in a file created higher up in my script
          Gysbert Wassenaar

          Your select statement doesn't specify which table it should get the data from, so nothing is returned and the table is not created with the fields you want.

          Try:

           

          select 

              e.employeenumber,

              e.surname,

              e.commencementdate as CommencementDate

          from e;

           

          If the table isn't named 'e' substitute it with the actual table name.

          And if you want to join FieldStaff on TotalHoursWorked then you'll probably want to make sure the field you join them on has the same name in both tables.

          • Re: How to access fields in a file created higher up in my script
            Don Green

            Thanks Gisbert. I left out the FROM cluase in my SQL, but do I have that in my code.

             

            What I want to know is how to reference a column in my FieldStaff table in my script, and use that value in my Load statement further down, in the Select section (ShiftDate - CommencementDate) (ShiftDate is from the QVD file, CommencementDate is from my FieldStaff table), or in a WHERE cluase when accessing the QVD file

            (WHERE ShifDate >= CommencementDate OR ShiftDate < CommencementDate + 30)

            or maybe

            WHERE ShifDate >= FieldStaff.CommencementDate OR ShiftDate < FieldStaff.CommencementDate + 30).