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: 
qv_testing
Specialist II
Specialist II

AND condition on same field - script level

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!

Labels (1)
2 Replies
zhadrakas
Specialist II
Specialist II

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

;

 

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.