Omit values in data load

[sanelisai]

Hi,

I have made a Sql query, in which I use command “not exists” in order to omit a group of IDs from the data. Qlik Sense’s data load doesn’t seem to understand my sql script. I don’t know QS-language at all, so I’m lost – How could I solve this?

SELECT distinct

x.customer,

x.ID

from dw.customers x

where not exists

(select z.ID from dw.othercustomers z

 Where c.ID = z.ID); 

How this is said in Qlik Sense?

[Gabriel_Araya · Accepted solution]

Hi ... you have two ways to do that ..

First: copy and paste your SQL statement inside editor script of Qlik .. like:

Customer:

SELECT distinct x.customer,  x.ID

from dw.customers x

where not exists

(select z.ID from dw.othercustomers z  Where c.ID = z.ID); 

The final table should have 2 fields: x.customer and x.ID

The second way is:

Load  Distinct ID ;

select .ID from dw.othercustomers;

Load  Customer, ID

Where not exists ( ID,ID);

SELECT distinct customer,  ID

from dw.customers;

Regards

[sanelisai]

Hi, 

and thank you for the answer! But I think the problem is the Sql command "not exists", which I should turn to something else, or try to do the data load in Qlik Sense language(?). 

[ArnadoSandoval · Accepted solution]

Hi @sanelisai 

If your data is at a database you still have to write the SQL statement, you initially posted this sql query

SELECT distinct
   x.customer,
   x.ID
from dw.customers x
where not exists
  (select z.ID 
   from dw.othercustomers z
   Where c.ID = z.ID);

Now, Did you run this query at your database engine? (TSql, Oracle, etc.); Does it work returning the data you expect?

You should first make sure that your DB query works within the database prior to including it into the Qlik script, so before giving you more directions, Would you please update us with the question asked above?

Thanks in advance

[sanelisai]

Hi,

I did try the script in Sql and it worked fine (there is an editing error in my example, but in real life it was ok). 

BUT - what I didn't do was testing the script alone in Qlik Sense. Now I tested it and it worked ok. So I have a problem somewhere else in my other scripts... 😳 sorry for the wrong question and thank you for your good advice!