Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

WHERE EXISTS OR SQL SELECT

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;

Tags (1)
8 Replies
bill_markham
Not applicable

Re: WHERE EXISTS OR SQL SELECT

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;

michael_anthony
Not applicable

Re: WHERE EXISTS OR SQL SELECT

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.

Not applicable

Re: WHERE EXISTS OR SQL SELECT

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.

Not applicable

Re: WHERE EXISTS OR SQL SELECT

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

robert99
Not applicable

Re: WHERE EXISTS OR SQL SELECT

Would this work

where exists (contact)

then concatenate

where not exists (Contact) and inactive = false

Although I'm sure there is a better way

neetha_p
Not applicable

Re: WHERE EXISTS OR SQL SELECT

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

Not applicable

Re: WHERE EXISTS OR SQL SELECT

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

robert99
Not applicable

Re: WHERE EXISTS OR SQL SELECT

Did you find a solution?