topic Re: WHERE EXISTS OR SQL SELECT in QlikView

WHERE EXISTS OR SQL SELECT

Wed, 17 Dec 2014 11:27:52 GMT

The statement below loads Contacts where the ContactID already exists AND where the Inactive_contact__c field is FALSE.

How can I amend it so that I load Contacts where the ContactID already exists OR where the Inactive_contact__c field is FALSE?

Contacts:

LOAD Id as ContactId,

AccountId,

Email,

Phone,

Inactive_Contact__c,

Name

WHERE EXISTS(ContactId,Id);

SQL SELECT *

FROM Contact

WHERE AccountId != NULL AND Inactive_Contact__c=FALSE;

Re: WHERE EXISTS OR SQL SELECT

Anonymous — Wed, 17 Dec 2014 11:34:43 GMT

How about something like this ?

Contacts:

LOAD Id as ContactId,

AccountId,

Email,

Phone,

Inactive_Contact__c,

Name

WHERE EXISTS(ContactId,Id)

or Inactive_Contact__c = 'FALSE';

SQL SELECT *

FROM Contact

WHERE AccountId != NULL AND Inactive_Contact__c=FALSE;

Re: WHERE EXISTS OR SQL SELECT

michael_anthony — Wed, 17 Dec 2014 11:41:25 GMT

Remove the Inactive_contact_id test from the SQL query and instead test in the load section for where exists(..) OR Inactive_contact_id = 'false'.

Note I've put the false in quotes. It looks luke your querying Salesforce and I think it passes false as a string literal. But may need to check.

Re: WHERE EXISTS OR SQL SELECT

Wed, 17 Dec 2014 11:50:46 GMT

Thanks Bill. I tried this and also removed the Inactive_Contact__c=FALSE from the SQL SELECT statement.

However, the results only brought back records that met the WHERE EXISTS clause. The OR part seems to have been ignored.

Re: WHERE EXISTS OR SQL SELECT

Wed, 17 Dec 2014 11:52:31 GMT

Thanks Michael. Yes, I am querying Salesforce. Good spot!

As i said in my reply to Bill, the OR part seems to have been ignored. As the number of records returned is exactly the same as if it wasn't there. There should be a big increase in the records returned

Re: WHERE EXISTS OR SQL SELECT

robert99 — Wed, 17 Dec 2014 12:10:06 GMT

Would this work

where exists (contact)

then concatenate

where not exists (Contact) and inactive = false

Although I'm sure there is a better way

Re: WHERE EXISTS OR SQL SELECT

Anonymous — Wed, 17 Dec 2014 12:25:19 GMT

Hi,

Try below code:

Contacts:
LOAD *;

SQL SELECT *

FROM Contact

WHERE AccountId != NULL;

NoConcatenate

Contact:
LOAD Id as ContactId,

AccountId,

Email,

Phone,

Inactive_Contact__c,

Name

Resident Contacts

WHERE EXISTS(ContactId,Id);

Concatenate

LOAD Id as ContactId,

AccountId,

Email,

Phone,

Inactive_Contact__c,

Name

Resident Contacts

WHERE Inactive_Contact__c= 'FALSE';

Regards

Neetha

Re: WHERE EXISTS OR SQL SELECT

Wed, 17 Dec 2014 14:47:50 GMT

Hi,
you could try comparing Inactive_Contact__c to the result of False() function, that is:

WHERE EXISTS(ContactId,Id) and Inactive_Contact__c=false()
;

Regards,

Anna

Re: WHERE EXISTS OR SQL SELECT

robert99 — Thu, 18 Dec 2014 19:11:06 GMT

Did you find a solution?