Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Urgently need some help.

Hello everyone, I would appreciate this if anyone could help me out with this question.

If I have hospitals as a field and three months of quantity preceding this column, i.e. 201307, 201308, 201309. I want to sort out the hospitals that have any two out of the three months >1 quantity. How can I do this? In Excel, this would have been countif(A2:A3, >1), followed by filter/sorting the table with >1. How can I achieve this in Qlikview?

Thanks,

Charles

8 Replies
jagan
Partner - Champion III
Partner - Champion III

Hi,

Please find attached file for solution hope it helps you.

Regards,

Jagan.

sujeetsingh
Master III
Master III

Hello see the sample i have pasted

Not applicable
Author

Dear Charles Liu,

Please try this.

Thank you.

Not applicable
Author

Thank you all for your quick responses. But I might have phrased the question wrong that I wasn't getting the results I wanted.

Please see the table below first:

QuantityQuantityQuantity
Hospital201307201308201309
Christie Hospital203
St. John's Hospital122
Philly Hospital002
Mount Sinai Hospital131
Criteria
MET (>1 quantity in at least 2 out of 3 months)
UNMET

What I want to achieve is when selecting "MET" in the field "Criteria", the table above would only show qualifying hospitals (like table below):

QuantityQuantityQuantity
Hospital201307201308201309
Christie Hospital203
St. John's Hospital122

I hope this clarifies my original question and I hope this would not be too much of a challenge for you qlikview gurus.

Thanks

Charles

Not applicable
Author

Hi,

    Check this attach file and let me know.

I hope this helps you.

Regards,

Ashutosh

jagan
Partner - Champion III
Partner - Champion III

Hi,

Try this in script,

 

Data:

LOAD
Hospital, M201307, M201308, M201309
WHERE Flag = 1;
LOAD
*,
If(Rangesum(If(M201307 > 1, 1), If(M201308 > 1, 1), If(M201309 > 1, 1)) >=2, 1) AS Flag
INLINE [
Hospital, M201307, M201308, M201309
Christie Hospital, 2, 0, 3
St. John's Hospital, 1, 2, 2
Philly Hospital, 0, 0, 2
Mount Sinai Hospital, 1, 3, 1]
;

Regards,

Jagan.

Not applicable
Author

hi ,

use the below expression in pivot .. it works for me which returns 2 hospital as per ur result.

if(aggr(Nodistinct sum(if(quantity>=2,1,0)),Hospital)>=2,sum(Quantity))

1.png


Regards

kumar.R

Not applicable
Author

Hello Charles,

Please find the attached document and the excel file. I have also added the filter.

Thanks,

Angad