topic Re: Load question - Where clause in resident table in QlikView

Load question - Where clause in resident table

Mon, 26 Aug 2013 16:44:35 GMT

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.

Re: Load question - Where clause in resident table

Gysbert_Wassenaar — Mon, 26 Aug 2013 16:58:24 GMT

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;

Re: Re: Load question - Where clause in resident table

Mon, 26 Aug 2013 18:27:13 GMT

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

Re: Load question - Where clause in resident table

Gysbert_Wassenaar — Mon, 26 Aug 2013 19:09:16 GMT

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;

Re: Load question - Where clause in resident table

Mon, 26 Aug 2013 19:17:46 GMT

Awesome! Thanks!