Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
qv_testing
Valued Contributor 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
Valued Contributor II

Re: AND condition on same field - script level

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

;

 

Highlighted
Digital Support
Digital Support

Re: AND condition on same field - script level

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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.