Where Statement not woking

[mazacini]

Hi Can someone tell me why this "Where" statement isn't working?

where year(DrpDate) >2013 and Not Exists (AccNo,[cl::code]);

even though this one does work

where year(DrpDate) >2013 and Exists (AccNo,[cl::code]);

[maxgro]

I run your script and get 2 records

I think the result it's ok

Do you agree?

[mazacini]

I have removed the Year condition, and the problem persists.

The first Where Exists statement works ok.

[mazacini]

Yep, that's what I get.

I'm trying to see if there is a pattern to the items not loaded..

[mazacini]

Hi Massimo

I see what is happening.

It is loading the first instance of every cl::code only.

So, once it loads the cl::code once, it then exists and so is ignored for second and subsequent instances.

Do you know how to overcome this?

Regards

Joe

[maxgro]

first load (with exists) only load 1 record, 2014 1

2012 1, 2012 3 are filtered out because of year filter, 2014 3 is excluded because of missing AccNo

second load (with not exists) only load 1 record, 2014 3

2012 1, 2012 3 are filtered out because of year filter, 2014 1 is excluded because you want not existent AccNo (and 1 exists)

[maxgro]

maybe I don't understand; tried with the same cl::code and it was loaded for second, etc..... instances

do you use the same script or you have a different one?

a:

load * inline [

AccNo

1

2

];

b:

load * inline [

DrpDate,cl::code

01/01/2012, 1

01/01/2012, 3

01/01/2014, 1

02/02/2014, 1

03/03/2014, 1

01/01/2014, 3

02/02/2014, 3

03/03/2014, 3

];

c:

load *,

0 as Field1

Resident b

where year(DrpDate) >2013 and Exists (AccNo,[cl::code]);

//NoConcatenate

load *,

1 as Field1

Resident b

where year(DrpDate) >2013 and Not Exists (AccNo,[cl::code]);

DROP Table a,b;

[mazacini]

Hi Massimo

Here is code ( I have replaced cl::code with CustCode)

// Load Interco Account Numbers, so as to allow initial flag of 'Interco' as CustType
Interco:

LOAD  AccNo

FROM

(ooxml, embedded labels, table is InterCompanyAcc);

// Load Jobs for Interco Account Numbers, 'Interco' as CustType where exists AccNo (ie Interco AccNo)
Jobs1:

LOAD CustCode as AccNo,
[cl::alpha],
Region,
JobNo,
BinTypID,
DrpDate,
Year(DrpDate) as DrpYr,
LftDate,
WasteTypeId,
[dk::value],
[dk::Wvalue],
'Interco' as CustType
FROM

(ooxml, embedded labels, table is JoeAccountJobsCurrent)

where Exists (AccNo,CustCode);

Drop Table  Interco;

//Now load Non-Interco, flag 'A/C' as CustType, where not exists AccNo

LOAD CustCode as AccNo,
[cl::alpha],
Region,
JobNo,
BinTypID,
DrpDate,
Year(DrpDate) as DrpYr,
LftDate,
WasteTypeId,
[dk::value],
[dk::Wvalue],
'Account' as CustType
FROM
FROM

(ooxml, embedded labels, table is JoeAccountJobsCurrent)

where  Not Exists (AccNo,CustCode);

[maxgro · Accepted solution]

it seems there is a little but important difference between previous source code (test code) and the last one

- in test code AccNo was static, loaded in first resident load with values 1 and 2; it never changed after that load

- in last code AccNo changes

I think you can change this and also exists not exists test to check ReferenceAccNo

Interco:
LOAD  AccNo as ReferenceAccNo
FROM

(ooxml, embedded labels, table is InterCompanyAcc);

[maxgro]

an example

//----------------------------------------------

// result is 1 row

a:

load * inline [

AccNo

1

];

b:

NoConcatenate load * where not exists (AccNo);

load * inline [

AccNo

1

1

2

2

];

drop table a;

//----------------------------------------------

// result is 2 row

c:

load AccNo as AccNoRef inline [

AccNo

1

];

d:

load * where not exists (AccNoRef,AccNo2);

load * inline [

AccNo2

1

1

2

2

];

drop table c;

[mazacini]

Hi Massimo

This worked! One modification - I left the Drop Table Interco until AFTER the where not exists load.

I don;t quite understand what is going on ie how the AccNo in my original script is "static"?

If you could explain, I would b grateful ( I have read your next post, but I'm afraid still don;t understand).

Anyway, many thanks for your help so far.

Joe