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

[#1]

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

(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

// (qvd)

//WHERE [IncludeTransaction] = 'Yes';

//

//DaysToFirstShift:

//Left Join     (FieldStaff)

//LOAD         

//            FieldStaffNo,

//            DaysToFirst AS DaysToFirstShift

//Resident    DaysToFirstShiftTable;

//Drop Table     DaysToFirstShiftTable;

[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.

[#3]

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).