topic Re: 'Match' function in Excel resemble function in Qlikview? in QlikView

'Match' function in Excel resemble function in Qlikview?

rupalighaste — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

In a Pivot, I've following expressions:

Sum(Price), Sum(Price1), Sum(Price2), Sum(Price3),Sum(Price4) ,Sum(Price5),Sum(Price6),Sum(Price7)

How can I find the largest value from (Sum(Price1), Sum(Price2)...., Sum(Price7)) whose value is <=Sum(Price). Here I need that value as well as column name like Price1, Price2 etc which is fulfilling the condition.

Re: 'Match' function in Excel resemble function in Qlikview?

sunny_talwar — Fri, 17 Nov 2017 18:47:53 GMT

May be this

RangeMax(Sum(Price), Sum(Price1), Sum(Price2)....)

Re: 'Match' function in Excel resemble function in Qlikview?

sunny_talwar — Fri, 17 Nov 2017 18:50:37 GMT

Or this

RangeMax(

If(Sum(Price1) <= Sum(Price), Sum(Price1), 0),

If(Sum(Price2) <= Sum(Price), Sum(Price2), 0),

If(Sum(Price3) <= Sum(Price), Sum(Price3), 0),

If(Sum(Price4) <= Sum(Price), Sum(Price4), 0),

If(Sum(Price5) <= Sum(Price), Sum(Price5), 0),

If(Sum(Price6) <= Sum(Price), Sum(Price6), 0),

If(Sum(Price7) <= Sum(Price), Sum(Price7), 0))

Re: 'Match' function in Excel resemble function in Qlikview?

rupalighaste — Tue, 21 Nov 2017 10:17:12 GMT

Hi, its giving me second highest value however is there any way to find column name/position also.

like RangeMax(Sum(Price1),Sum(Price2),Sum(Price3) gives me actual value but how to get column name/position for that associated value?

Re: 'Match' function in Excel resemble function in Qlikview?

sunny_talwar — Tue, 21 Nov 2017 13:06:18 GMT

I am sure there must be a way, would you be able to share some data to see what we are looking at?

Re: 'Match' function in Excel resemble function in Qlikview?

rupalighaste — Wed, 22 Nov 2017 11:09:54 GMT

Its done. I just tried to match the Rangemax() value with all price columns to find the position/name of the column. But I didn't find any built in function for this in Qlikview . Anyways I got my solution thank you so much for your help on this as with your provided solution I was able to continue. Thanks!

Re: 'Match' function in Excel resemble function in Qlikview?

sunny_talwar — Thu, 23 Nov 2017 23:33:41 GMT

Awesome, I am glad you managed to solve this