Solved: If match and match - Qlik Community

cbaqir · ‎2018-12-17

Can someone help me figure out why this isn't working? I want to show only DEFECT_ID that have hit both 'Regional Clinical Informatics' and 'ART - AMB Refinement Team'.

=if(match(COM_CAT,'Regional Clinical Informatics') and match(COM_CAT,'ART - AMB Refinement Team'),DEFECT_ID)

marcus_sommer · ‎2018-12-18

If you used a table-chart and displays there COM_CAT and the DEFECT_ID you could see them in their context and you could try to apply a logic like I mentioned before (whereby you will probably need some TOTAL statement within the expression to get it to work by this granularity).

If I interpret your data right then they are chains of events - one ID changed their state several times on multiple dates. This is in general not easy to handle and I suggest to transfer as much as possible of the logic into the script (categorizing and flagging the multiple states and using multiple and/or canoncal calendars and/or as-of-tables) instead of trying to solve such tasks within the UI.

- Marcus

View solution in original post

marcus_sommer · ‎2019-01-05

It's not clear for me where do you want to apply this expression and it's further quite hard to comprehend your datamodel - at least in a quick glance.

I think like above mentioned that you need to apply the logic of categorizing and/or at least flagging them and the chain of events within the datamodel and not trying to create this within the UI. Not everything possible in the script could be done in the UI and if it's often much harder to get and by larger datasets it might be not performant enough. Therefore, I suggest that you rethink your datamodel.

- Marcus

View solution in original post

Anil_Babu_Samineni · ‎2018-12-17

Can't you use this?
=if(match(COM_CAT,'Regional Clinical Informatics', 'ART - AMB Refinement Team'),DEFECT_ID)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

marcus_sommer · ‎2018-12-18

Your match happened on record-level and there could COM_CAT have only one value and therefore your check for two values failed. This means you will probably need to aggregate COM_CAT to apply such filter - maybe with something like this:

if(aggr(count({< COM_CAT = {'Regional Clinical Informatics','ART - AMB Refinement Team'}>} distinct DEFECT_ID), DEFECT_ID) = 2, DEFECT_ID)

- Marcus

sunny_talwar · ‎2018-12-18

Are you using this in the script or front end of the app?

cbaqir · ‎2018-12-18

I believe that gives me an OR when I am looking for an AND.

cbaqir · ‎2018-12-18

In the chart dimension.

cbaqir · ‎2018-12-18

Thanks Marcus. Is there a way to see any records with those two COM_CAT selections? In some cases, either of them could have happened more than once and I need to capture all of them. My goal is to show the total time in the Regional Clinical Informatics bucket AFTER going to ART. That means exluding any time in the RCI bucket before the ticket FIRST went to ART.

sunny_talwar · ‎2018-12-18

May be handle this in your expression using set analysis

Dimension

DEFECT_ID

Expression

Sum({<DEFECT_ID = p({<COM_CAT = {'Regional Clinical Informatics'}>})*p({<COM_CAT = {'ART - AMB Refinement Team'}>})>}Measure)

cbaqir · ‎2018-12-18

My goal is to show the total time in the Regional Clinical Informatics bucket AFTER going to ART. That means excluding any time in the RCI bucket before the ticket FIRST went to ART.

To calculate the time I have to find COM_CATE_END_DATE - COM_CAT_START_DATE of any time where COM_CAT = 'Regional Clinical Informatics ' AFTER the first time it went to COM_CAT ='ART - AMB Refinement Team'.

I realize that is complicated which is why I was working on one step at a time but also why I am not sure if I can do this in the expression.

sunny_talwar · ‎2018-12-18

Would you be able to explain this with some sample data as to what exactly you are trying to do?

If match and match

Related Topics