Exists() behaves differently in select vs where clause??

[#1]

Hi I am trying to use "where not exists(a,b)" to load data from a table to retrieve data which (surprise surprise) exists in the first table but not the other.

The columns I am comparing on are keys generated by autoid.

RawInv:
LOAD
RawInvDistiName as InvDistiName,
date(RawInvReportDate, 'DD/MM/YYYY') as RawInvReportDate,
RawInvQuantity,
RawInvProductCode,
RawInvEanCode,
RawInvEnteredDate,
autonumber(RawInvDistiName&RawInvProductCode&date(RawInvReportDate, 'DD/MM/YYYY')&RawInvQuantity) as RawInvUniqueId
FROM [..\Qvd\RawInventory.qvd] (qvd);
ExcelTableInv:
noconcatenate load
DistiName as InvDistiName,
date(InventoryDate, 'DD/MM/YYYY') as ExcelInvReportDate,
ProductCode as ExcelInvProductCode,
StockQuantity as ExcelInvQuantity,
autonumber(DistiName&ProductCode&date(InventoryDate, 'DD/MM/YYYY')&StockQuantity) as ExcelInvUniqueId;
SQL SELECT DistiName,
InventoryDate,
ProductCode,
StockQuantity
FROM QV_ExcelReportInvAll;
MissingInExcelInv:
noconcatenate LOAD
InvDistiName,
RawInvReportDate ,
RawInvQuantity ,
RawInvProductCode ,
RawInvEanCode ,
RawInvEnteredDate ,
RawInvUniqueId ,
if(exists(ExcelInvUniqueId, RawInvUniqueId), 'data matches', 'the uniqueid does not exist in excel') as check
resident RawInv
where not exists(ExcelInvUniqueId, RawInvUniqueId) ;

Basically, the problem is that I know that the data in the two top tables matches.

If i remove the where clause in the last select, i get 'data matches' in the check value to confirm.

But somehow the where does not seem to work correctly and displays all the data as not existing.

What am I getting wrong here?

thanks for your help!

[#2 · Accepted solution]

Hi,

the first Field Name has to stand in qotes ''. Thats the reference field the second one stand for the Values in the loaded table.

I'm afraid thats wrong in the QlikView-Help.

Have Fun with QV

Alex:)

[#3]

Hi Alex, thanks for your reply.

I tried double quotes, but still no joy. I get the same result. The exists condition works fine in the select statement without quotes anyway. Seems rather strange...

[#4]

Try single quotes, sorry.

Alex:)

[#5]

Dont single quotes make it a string?

[#6]

Yes, but you have to give the field name as string to the "function".

Regards Alex:)

[#7]

OKay, that might explain a few things! I'll try this tomorrow, thanks for your help.

[#8]

Hi, that worked. Thanks! Strange that it would work in the select but not in the where. I couldnt figure out what was going on!