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

Issue with where not exists()

Hi guys, stalwar1

Isn't this supposed to return :

alert2, closed

alert4, commented

?

What I want to have, is if the alert is commented and closed; i just keep the closed line.


Alertes:

load alerte, date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') as date, status;

load * Inline [

alerte, date, status

alerte1, 16/10/2017, non commented

alerte2, 15/10/2017, commented

alerte2, 15/10/2017, closed

alerte3, 15/10/2017, non commented

alerte4, 15/10/2017, commented

];

NoConcatenate

alertesNonCommentées:

load * Resident Alertes where status='non commented';

NoConcatenate

alertesCommentéesClosed:

load * Resident Alertes   where status<>'non commented';

NoConcatenate

alertesClosed:

load * Resident alertesCommentéesClosed where status='closed'; // returns alert2 , closed

load * where not exists(alerte);

load * Resident alertesCommentéesClosed where status<>'closed'; //return alert2, commented and alert4, commented

// I want to return alert2, closed and alert4,commented

drop table alertesCommentéesClosed;

drop Table alertesNonCommentées;

drop Table Alertes;

result:

Capture.PNG

what am I doiing wrong?

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Yiou need different logic here. All the alerte value exist so the not exists will fail. Try something like this (after loading the 'alertes' table:

alertesClosed:

LOAD alerte,

  alerte as _closed,

  date,

  status

Resident Alertes

Where status = 'closed';

Concateneta(alertesClosed)

LOAD alerte,

  date,

  status

Resident Alertes

Where status <> 'non-commented' And status <> 'closed'

  And Not(Exists(_closed, alerte);

DROP field _closed;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

7 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Yiou need different logic here. All the alerte value exist so the not exists will fail. Try something like this (after loading the 'alertes' table:

alertesClosed:

LOAD alerte,

  alerte as _closed,

  date,

  status

Resident Alertes

Where status = 'closed';

Concateneta(alertesClosed)

LOAD alerte,

  date,

  status

Resident Alertes

Where status <> 'non-commented' And status <> 'closed'

  And Not(Exists(_closed, alerte);

DROP field _closed;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
antoniotiman
Master III
Master III

May be this

Temp:
load alerte, date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') as date, status;
load * Inline [
alerte, date, status
alerte1, 16/10/2017, non commented
alerte2, 15/10/2017, commented
alerte2, 15/10/2017, closed
alerte3, 15/10/2017, non commented
alerte4, 15/10/2017, commented
]
;
Inner Join
LOAD alerte,MinString(status) as status
Resident Temp
Where Match(status,'commented','closed')
Group By alerte,date;

OmarBenSalem
Author

That worked perfectly !

Thanks !

OmarBenSalem
Author

What a great way to see things! brilliant !

OmarBenSalem
Author

Sorry for bothering you, but whta If I had this :

Alertes:

load alerte, date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') as date, status;

load * Inline [

alerte, date, status

alerte1, 16/10/2017, non commented

alerte1, 16/10/2017, commented

alerte2, 15/10/2017, commented

alerte2, 15/10/2017, closed

alerte3, 15/10/2017, non commented

];

And wanted to 1st, verify, if the same Alert, in our case, alerte1 is at the same time commented and non commented, we just keep the commented one; then verify if it's commented and closed (alert 2); the previous example, we keep the closed line.

so as a result:

alerte1, commented

alerte2, closed

alerte3, non commented

thanks a ton !

OmarBenSalem
Author

antoniotiman
Master III
Master III

May be this ?

LOAD alerte,date,MinString(status) as status
Inline [
alerte, date, status
alerte1, 16/10/2017, non commented
alerte1, 16/10/2017, commented
alerte2, 15/10/2017, commented
alerte2, 15/10/2017, closed
alerte3, 15/10/2017, non commented]

Group By alerte,date
;