Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI Community,
Can you please help me.
Need to Calculate – which ID has status both Open and Closed.
LOAD * INLINE [
ID, Status, Date
100, Open, 01/08/2019
100, Closed, 05/10/2019
101, Open, 22/11/2019
102, Open, 10/11/2019
102, Pending, 22/11/2019
102, Closed, 10/12/2019
];
OutPut:
ID, Status, Date
100, Open, 01/08/2019
100, Closed, 05/10/2019
102, Open, 10/11/2019
102, Closed, 10/12/2019
Have written like this - but not working.
where Status='Open' and Status='Closed';
Thanks In Advance!
that will not work cause that condition will check if there is one row that fits both conditions. Thats not possible.
maybe you want to do it like this:
1) Add the field to your table
if(Status='Open', 1,0) as FLAG_OPEN,
if(Status='Closed', 1,0) as FLAG_CLOSED,
2) then you can create an aggregated table
Aggregated:
Load ID,
if(OPEN_STAGE=1 AND CLOSED_STAGE=1, 1,0) as FLAG_OPEN_AND_CLOSED
Load ID,
max(FLAG_OPEN) as OPEN_STAGE,
max(FLAG_CLOSED) as CLOSED_STAGE
Resident Input
GROUP BY ID
;
Did the suggestion from the post help you with the use case? If so, be sure you return to the post and use the Accept as Solution button on that post to give the poster credit for the assistance and let other Community Members know the solution worked.
If you are still trying to sort things out, leave an update with what additional help you require. The only other thing I have is a link to the Design Blog area of Communiity, you can search there to see if you can find something that might fit too. There are usually multiple ways to do things, but the post should work from what I can see, not sure if there is something more efficient potentially or not. My post will also kick things back up, and someone else may have another look and potentially offer up a different idea too.
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
Regards,
Brett