Rules for repeated LOAD statements against same data

tschullo — Tue, 18 Apr 2017 19:24:54 GMT

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.

topic Rules for repeated LOAD statements against same data in QlikView

Rules for repeated LOAD statements against same data