Why does this IF (Exists not work?

[mazacini]

Here is my script:

___________________________________

SORDTEMP:

LOAD REF as SORDREF

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',')

WHERE DATE > '31/12/2010';

SORDITEM:

LOAD IF (Exists (SORDREF,REF), REF) as SORDREF,

ITEMREF,

ACCCODE,

NOMCODE

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',');

________________

Note - SORDTEMP - only loads orders that have been created in 2011 from the Order Header fle SORDERS1.csv.

SORDITEM - should load details fron file SORDITEM1.csv, but ONLY for those orders for which an order REF has been loaded into SORDTEMP

Any suggestions?

[johnw · Accepted solution]

The second load will load all records from the CSV. All you're doing is nulling out the SORDREF field when it wasn't in the other file, but the other fields will all load in. To avoid loading the rows at all, use where(exits(...)) like this:

SORDITEM:
LOAD REF as SORDREF,
ITEMREF,
ACCCODE,
NOMCODE
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',')
WHERE exists(SORDREF,REF)
;

[vgutkovsky]

Move the exists() condition to a WHERE statement after the second table load, instead of an IF statement within the load itself.

Regards,

[pover]

Try doing one following:

SORDITEM:

LOAD REF as SORDREF,

ITEMREF,

ACCCODE,

NOMCODE

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',')

where Exists (SORDREF,REF);

or

SORDITEM:

RIGHT KEEP (SORDTEMP)

LOAD REF as SORDREF,

ITEMREF,

ACCCODE,

NOMCODE

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',');

Each way should only load the REF's in SORDITEM that are also in SORDTEMP.

Regards.

[pover]

Edit: Where did the option to delete a post go???

Try doing one following:

SORDITEM:

LOAD REF as SORDREF,

ITEMREF,

ACCCODE,

NOMCODE

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',')

where Exists (SORDREF,REF);

or

SORDITEM:

RIGHT KEEP (SORDTEMP)

LOAD REF as SORDREF,

ITEMREF,

ACCCODE,

NOMCODE

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',');

Each way should only load the REF's in SORDITEM that are also in SORDTEMP.

Regards.

[mazacini]

Hi Guys

Where Exists looks good - I will audit outcome properly and verify your suggestions accordingly.

Karl - will also try your second suggestion and verify if ok.

Great response! This realy is a great community!

Joe

[mazacini]

Hi Karl

The first script worked for me. The second didn't.

The second loads all records from file SORDITEM1.csv

Joe

[pover]

Yeap, you're right. I can't seem to get my left and right straight. You should use a Left Keep instead of a Right Keep.

I mentioned this solution because it is sometimes faster when loading large QVD's because the where statement will slow the QVD load while the Left Keep will let the QVD load fast and then filter out the rows you don't need.

However, a quick test reveals that a "where exists" allows the the QVD to loaded as QVD Optimized (Fast), but a "where field=value" slows done the QVD load.

Regards.

[johnw]

Karl Pover wrote:...a quick test reveals that a "where exists" allows the the QVD to loaded as QVD Optimized (Fast), but a "where field=value" slows done the QVD load.

Yep, you're allowed a single "where exists" in an optimized load. So far as I know, that's the ONLY thing you can put in the "where" without slowing it down. Even a second "where exists" will slow it down.