topic Re: Where Statement not woking in QlikView

Where Statement not woking

mazacini — Wed, 24 Sep 2014 16:05:00 GMT

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]);

Re: Where Statement not woking

demoustier — Wed, 24 Sep 2014 16:08:34 GMT

Did you try without ( before 'Not' ?

Re: Where Statement not woking

mazacini — Wed, 24 Sep 2014 16:15:58 GMT

Apologies - the ( was my attempt at a solution.

I have edited the script in my original post, which is without the (..)

Re: Where Statement not woking

anbu1984 — Wed, 24 Sep 2014 16:39:37 GMT

Are you getting any result or nothing?

Check whether DrpDate is date and you are getting year from year(DrpDate)

Re: Where Statement not woking

mazacini — Wed, 24 Sep 2014 17:27:18 GMT

I am getting a partial load of the records.

I just loaded in year(DrpDate) as DrpYr and there are values for that field for the partially loaded records

Thanks

Re: Where Statement not woking

anbu1984 — Wed, 24 Sep 2014 17:39:10 GMT

Can you post your script

Re: Where Statement not woking

maxgro — Wed, 24 Sep 2014 17:51:07 GMT

it seems your script works; well this is is a small test and I didn't get any error

please post your script

load * inline [

AccNo

];

load * inline [

DrpDate,cl::code

01/01/2012, 1

01/01/2012, 3

01/01/2014, 1

01/01/2014, 3

];

NoConcatenate

load *

Resident b

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

DROP Table a,b;

Re: Where Statement not woking

arniewolff — Wed, 24 Sep 2014 18:06:53 GMT

Just to make sure I understand what you're trying to do:

This line:

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

should load data for years greater than 2013 when [cl::code] is not in AccNo of the records loaded so far.

That's how I understand the code. Is that the intent?

Arnie

Re: Where Statement not woking

arniewolff — Wed, 24 Sep 2014 18:11:49 GMT

Only the record 01/01/2014, 3 is loaded as expected. Year is greater than 2013 and the [cl::code] did not exist in the previous records loaded.

Good example!

Arnie

Re: Where Statement not woking

mazacini — Wed, 24 Sep 2014 19:47:59 GMT

I have slightly modified your code to reflect what it is I am actually doing, and it is working fine.

The problem with my live data is that only some of the expected records are loading.

Here is the modified script:

load * inline [

AccNo

];

load * inline [

DrpDate,cl::code

01/01/2012, 1

01/01/2012, 3

01/01/2014, 1

01/01/2014, 3

];

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;

Re: Where Statement not woking

maxgro — Wed, 24 Sep 2014 20:12:23 GMT

I run your script and get 2 records

I think the result it's ok

Do you agree?

Re: Where Statement not woking

mazacini — Wed, 24 Sep 2014 20:17:44 GMT

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

The first Where Exists statement works ok.

Re: Where Statement not woking

mazacini — Wed, 24 Sep 2014 20:18:46 GMT

Yep, that's what I get.

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

Re: Where Statement not woking

mazacini — Wed, 24 Sep 2014 20:24:31 GMT

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

Re: Where Statement not woking

maxgro — Wed, 24 Sep 2014 20:33:53 GMT

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)

Re: Where Statement not woking

maxgro — Wed, 24 Sep 2014 20:50:24 GMT

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?

load * inline [

AccNo

];

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

];

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;

Re: Where Statement not woking

mazacini — Wed, 24 Sep 2014 21:02:37 GMT

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);

Re: Where Statement not woking

maxgro — Wed, 24 Sep 2014 21:12:14 GMT

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);

Re: Re: Where Statement not woking

maxgro — Wed, 24 Sep 2014 21:32:53 GMT

an example

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

// result is 1 row

load * inline [

AccNo

];

NoConcatenate load * where not exists (AccNo);

load * inline [

AccNo

];

drop table a;

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

// result is 2 row

load AccNo as AccNoRef inline [

AccNo

];

load * where not exists (AccNoRef,AccNo2);

load * inline [

AccNo2

];

drop table c;

Re: Where Statement not woking

mazacini — Wed, 24 Sep 2014 21:44:18 GMT

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