topic AW:Re: AW:Re: AW:Exists() behaves differently in select vs where clause?? in QlikView

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

Tue, 01 Dec 2009 22:15:48 GMT

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!

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

Tue, 01 Dec 2009 23:06:07 GMT

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

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

Tue, 01 Dec 2009 23:19:52 GMT

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...

AW:Re: AW:Exists() behaves differently in select vs where clause??

Tue, 01 Dec 2009 23:23:00 GMT

Try single quotes, sorry.

Alex:)

AW:Re: AW:Exists() behaves differently in select vs where clause??

Tue, 01 Dec 2009 23:27:43 GMT

Dont single quotes make it a string?

AW:Re: AW:Re: AW:Exists() behaves differently in select vs where clause??

Tue, 01 Dec 2009 23:41:12 GMT

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

Regards Alex:)

AW:Re: AW:Re: AW:Exists() behaves differently in select vs where clause??

Wed, 02 Dec 2009 00:56:43 GMT

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

AW:Re: AW:Re: AW:Exists() behaves differently in select vs where clause??

Wed, 02 Dec 2009 17:44:53 GMT

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!