Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
what am I doiing wrong?
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;
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;
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;
That worked perfectly !
Thanks !
What a great way to see things! brilliant !
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 !
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;