Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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
;