Load question - Where clause in resident table

[#1]

Hi All,

I have a special requirement to identify the previous order date for a set of orders.

Here is a simple load script in which I am trying to simulate the scenario.

For some reason I am getting a field not found error. Would some one please help me?

[OLD_DRD]:
load * inline
[ACCID,ORDDT_OLD
A123,5/1/2012
A123,2/1/2013
A123,3/1/2013
A123,5/1/2013
A990,9/2/2012
A990,1/1/2013
A990,7/1/2013
A990,8/15/2013];

[CHK_ORD]:
load * inline
[ACC_ID_N, ORD_NEW
A123,4/30/2013
A990,8/1/2013
A990,6/1/2013
];

left join (CHK_ORD)  load
ACCID as ACC_ID_N,
max(ORDDT_OLD) as LAST_ORD_DT
resident OLD_DRD where Exists(ACC_ID_N , ACCID) and ORDDT_OLD < ORD_NEW;
//or something like this
//               where ACCID = ACC_ID_N and ORDDT_OLD < ORD_NEW;

// Final Table will look like this
// ACC_ID_N,  ORD_NEW,    LAST_ORD_DT
// A123,      4/30/2013,  3/1/2013
// A990,      8/1/2013,   7/1/2013
// A990,      6/1/2013,   1/1/2013


drop table OLD_ORD;

Thanks,

Aji Paul.

[Gysbert_Wassenaar]

Your [OLD_DRD] table doesn't have a field named ORD_NEW. Also, your last load statement is missing a group by clause:

left join (CHK_ORD) load
ACCID as ACC_ID_N,
max(ORDDT_OLD) as LAST_ORD_DT
resident OLD_DRD where Exists(ACC_ID_N , ACCID) and ORDDT_OLD < ORD_NEW

group by ACCID;

[#3]

Hi, Thanks for the reply.

What I am looking for is a final table as shown

// Final Table will look like this
// ACC_ID_N,  ORD_NEW,    LAST_ORD_DT
// A123,      4/30/2013,  3/1/2013
// A990,      8/1/2013,  7/1/2013
// A990,      6/1/2013,  1/1/2013

[Gysbert_Wassenaar · Accepted solution]

Try:

[OLD_DRD]:

load * inline

[ACCID,ORDDT_OLD

A123,  5/1/2012

A123,  2/1/2013

A123,  3/1/2013

A123,  5/1/2013

A990,  9/2/2012

A990,  1/1/2013

A990,  7/1/2013

A990,  8/15/2013];

[CHK_ORD]:

load * inline

[ACC_ID_N, ORD_NEW

A123,  4/30/2013

A990,  8/1/2013

A990,  6/1/2013];

left join (CHK_ORD)  load

ACCID as ACC_ID_N,

ORDDT_OLD

resident OLD_DRD;

Result:

NoConcatenate

load

ACC_ID_N,

ORD_NEW,

max(if(ORDDT_OLD<ORD_NEW,ORDDT_OLD)) as ORDDT_OLD

Resident CHK_ORD

group by ACC_ID_N,ORD_NEW;

drop table OLD_DRD, CHK_ORD;

[#5]

Awesome! Thanks!