Calculated field in previous load cannot be used in subsequent load

[umashankarus]

Hi Experts,

Facing this issue - Field not found error - MAX_CREATION_DATE

Script:

LOAD DOC_ID,
     VERSION_NUM,
     STATUS_CODE,
     CREATION_DATE,
     LAST_UPDATE_DATE
FROM

(ooxml, embedded labels, table is Sheet1);

LOAD
DOC_ID,VERSION_NUM,STATUS_CODE,max(DATE(CREATION_DATE)) as MAX_CREATION_DATE
Resident Sheet1 Group by DOC_ID,VERSION_NUM,STATUS_CODE ;

LOAD
DOC_ID,VERSION_NUM,STATUS_CODE, MAX_CREATION_DATE,
IF( STATUS_CODE = 'PENDING_APPROVAL', TODAY()-CONSTHIST_CREATION_DATE) as sApplPendingDays
RESIDENT Sheet1;

What am I missing here ? and can you pl explain the cause of the error ?

[maxgro · Accepted solution]

I think this

//

// load from excel and call the table Table1

//

Table1:

LOAD DOC_ID,

    VERSION_NUM,

    STATUS_CODE,

    CREATION_DATE,

    LAST_UPDATE_DATE

FROM  (ooxml, embedded labels, table is Sheet1);

//

// calc the max creation date from Table1 and left join to Table1

//

Left Join (Table1)

LOAD

DOC_ID,VERSION_NUM,STATUS_CODE,max(DATE(CREATION_DATE)) as MAX_CREATION_DATE

Resident Table1

Group by DOC_ID,VERSION_NUM,STATUS_CODE ;

//

// Here I get an error because there isn't any CONSTHIST_CREATION_DATE field

//

Table2:

LOAD

DOC_ID,VERSION_NUM,STATUS_CODE, MAX_CREATION_DATE,

IF( STATUS_CODE = 'PENDING_APPROVAL', TODAY()-CONSTHIST_CREATION_DATE) as sApplPendingDays

RESIDENT Table1;

DROP Table Table1;

[umashankarus]

Thanks for the help here