Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

omarbensalem
Esteemed Contributor

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
MVP
MVP

Re: Issue with where not exists()

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;

7 Replies
MVP
MVP

Re: Issue with where not exists()

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;

antoniotiman
Honored Contributor III

Re: Issue with where not exists()

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
Esteemed Contributor

Re: Issue with where not exists()

That worked perfectly !

Thanks !

omarbensalem
Esteemed Contributor

Re: Issue with where not exists()

What a great way to see things! brilliant !

omarbensalem
Esteemed Contributor

Re: Issue with where not exists()

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
Esteemed Contributor

Re: Issue with where not exists()

antoniotiman
Honored Contributor III

Re: Issue with where not exists()

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
;