Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
waleeed_mahmood
Creator
Creator

SQL except in QS

Hi all,

I have a question related to QS and SQL. I have seen that you can load values from a table using an EXCEPT expression in SQL. So, for example,

 

 

Load * from T1

EXCEPT

Load * from T2;

 

 

This will load all values from T1 except those values that exist in the T2. 

I would like to do the same in QS, if possible. I have two tables T1 and T2 in SQLMS i want to read in T1 except the rows T2 has, that have been marked as delete. I would like to keep the rows in my original table but simply not load those rows in QS. 

T1 has many columns but T2 has enough columns that can uniquely identify a row in T1.

Is it possible? if yes, could someone please provide a sample script?

Thank you.

Labels (2)
1 Solution

Accepted Solutions
albertovarela
Partner - Specialist
Partner - Specialist

Take a look at the Exists function.  "Exists() determines whether a specific field value has already been loaded into the field in the data load script. The function returns TRUE or FALSE, so can be used in the where clause of a LOAD statement or an IF statement."

View solution in original post

3 Replies
albertovarela
Partner - Specialist
Partner - Specialist

Take a look at the Exists function.  "Exists() determines whether a specific field value has already been loaded into the field in the data load script. The function returns TRUE or FALSE, so can be used in the where clause of a LOAD statement or an IF statement."

crichter14
Creator
Creator

I'm not sure I understand the example.  Do you use EXIST like a where clause and list out each field?

albertovarela
Partner - Specialist
Partner - Specialist

The function compares a field or an expression declaring to several fields in the current load, against a single field already loaded via the script.

If you'd like to compare a full record (several fields) you could create a concatenated field with all your fields (maybe hash or Autonumber),  and then use it with the Exists in the where clause.

Here's one example. I hope it helps 

 

2020-11-05_10-20-03.png