Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Load question - Where clause in resident table

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.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Load question - Where clause in resident table

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;


talk is cheap, supply exceeds demand
4 Replies
MVP & Luminary
MVP & Luminary

Re: Load question - Where clause in resident table

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;


talk is cheap, supply exceeds demand
Not applicable

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

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




MVP & Luminary
MVP & Luminary

Re: Load question - Where clause in resident table

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Load question - Where clause in resident table

Awesome! Thanks!

Community Browser