Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mazacini
Creator III
Creator III

Where Statement not woking

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

View solution in original post

23 Replies
demoustier
Creator
Creator

Did you try without ( before 'Not' ?

mazacini
Creator III
Creator III
Author

Apologies - the ( was my attempt at a solution.

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

anbu1984
Master III
Master III

Are you getting any result or nothing?

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

mazacini
Creator III
Creator III
Author

Hi

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

J

anbu1984
Master III
Master III

Can you post your script

maxgro
MVP
MVP

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

please post your script


a:

load * inline [

AccNo

1

2

];

b:

load * inline [

DrpDate,cl::code

01/01/2012, 1

01/01/2012, 3

01/01/2014, 1

01/01/2014, 3

];

c:

NoConcatenate

load *

Resident b

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

DROP Table a,b;

arniewolff
Contributor III
Contributor III

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

arniewolff
Contributor III
Contributor III

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

mazacini
Creator III
Creator III
Author

Hi

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:

a:

load * inline [

AccNo

1

2

];

b:

load * inline [

DrpDate,cl::code

01/01/2012, 1

01/01/2012, 3

01/01/2014, 1

01/01/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;