Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]);
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
(
Did you try without ( before 'Not' ?
Apologies - the ( was my attempt at a solution.
I have edited the script in my original post, which is without the (..)
Are you getting any result or nothing?
Check whether DrpDate is date and you are getting year from year(DrpDate)
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
Can you post your script
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;
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
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
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;