I'm wondering if someone could point me to a clear discussion of the rules for loading data using multiple load statements to add fields to the initial load.
For example:
Table1
EmplID Year CourseName EmpYear_KEY
101 2017 QV101 1
101 2017 QV102 1
102 2017 QV101 2
Table2
EmplID Country HireDate
101 US 2/4/2016
102 UK 2/4/2017
HourReqs:
// <------------------------------------------------------------------------------------- Insert point #1
// First I need a distinct list of Employee/Yr combinations.
// I will use the EmpYear_KEY to link to Table1 but need the fields for next steps...
LOAD Distinct EmplID,
Year,
EmpYear_KEY
Resident table1;
//Next I tack on the country and Hire Date
Left Join(Table1)
LOAD EmplID,
Country as EmplCtry,
HireDate as [Hire Date]
Resident Table2;
//Now I would like to calculate some new fields
Ideally, I could add some code like this at "Insert point #1" above:
LOAD *,
Ceil(ReqHrsFactor*FullYearReqdHrs) as [Required Hours];
LOAD *,
if(Year([Hire Date])<Year,1,
if(Year([Hire Date])>Year,0,
DayNumberOfYear([Hire Date])/366)) as ReqHrsFactor, For pro-rated reqd hrs factor
If(EmplCtry = 'US',56,ApplyMap('GCA_OverrideMap',[Employee ID],35)) as FullYearReqdHrs;
But that didnt't work so i think I'm going to have to continue to do more joins on itself or use another table.
I just would like to better understand what the rules are around mutliple calls to LOAD for same set of data.
