topic Re: AND condition on same field - script level in QlikView

AND condition on same field - script level

Nagaraju_KCS — Sat, 16 Nov 2024 01:42:56 GMT

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!

Re: AND condition on same field - script level

zhadrakas — Thu, 12 Dec 2019 09:50:19 GMT

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 ;

Re: AND condition on same field - script level

Brett_Bleess — Wed, 18 Dec 2019 19:40:23 GMT

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