topic Re: Second Max Value in Column Range (Straight Table) in QlikView

Second Max Value in Column Range (Straight Table)

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

Hi Guys,

Can you please advise me how can I get second or third max value in column range?

Col_1	Col_2	Col_3	Col_4	Col_5
10	20	30	40	50

Currently I am using RangeMax & RangeMin to get Max or Min value. But I get to get 2 or 3 from the range.

Kind regards,

Farrukh

Re: Second Max Value in Column Range (Straight Table)

settu_periasamy — Fri, 09 Feb 2018 21:25:49 GMT

Hi,

Col_1, Col_2.. etc are Label of expressions, right? Then try like this..(for 3rd Max)

Pick(Match(

RangeMax(Col_1,Col_2,Col_3,Col_4,Col_5),

Col_1,Col_2,Col_3,Col_4,Col_5)-2,

Col_1,Col_2,Col_3,Col_4,Col_5)

Re: Second Max Value in Column Range (Straight Table)

Anil_Babu_Samineni — Sat, 10 Feb 2018 05:51:06 GMT

I believe, Pick(Match(... Won't support expression like you said. I think we can create Key for all columns and we can use same?

Thread Owner - What is the output you want to get from given input?

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Sat, 10 Feb 2018 16:52:17 GMT

Hi Settu,

It did not work

Farrukh

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Sat, 10 Feb 2018 16:56:53 GMT

Hi Anil,

I have 5 columns where the numerical data exist.

Col_1	Col_2	Col_3	Col_4	Col_5
10	20	30	40	50

I want to pick nth value as per result, it could be first value , second value, or any from above 5 columns.

Kind regards,

Farrukh

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Sat, 10 Feb 2018 17:02:16 GMT

Hi Anil,

I have written the formula in excel and its giving me correct result.

I am looking to write an expression in Qlikview.

Index Created: INDEX($BL3:$BP3

Max Value as per Input ((LARGE($BL3:$BP3,1)

And then Match

=IF(DN3>0,

IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,1)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"1H",

IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,2)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"2H",

IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,3)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"3H",

IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,4)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"2L",

IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,5)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"1L"))))),"")

Can you please advise me related to this? How can I achieve in Qlikvew.

Kind regards,

Farrukh

Re: Second Max Value in Column Range (Straight Table)

Anil_Babu_Samineni — Sat, 10 Feb 2018 17:10:03 GMT

Please share Expected result set?

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Sat, 10 Feb 2018 17:45:49 GMT

Hi Anil

Please see the attached excel sheet where the excel formula built-in.

Regards,

Farrukh

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Sat, 10 Feb 2018 17:49:10 GMT

Hi Settu,

Please see the attached excel sheet where the excel formula built-in.

Can you please advise me how to get the similar result in excel.

Regards,

Farrukh

Re: Second Max Value in Column Range (Straight Table)

Anil_Babu_Samineni — Sat, 10 Feb 2018 18:07:13 GMT

What you got, When you use this?

If(Sum(Seq_Count_1)>0,

If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),1)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '1H',

If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),2)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '2H',

If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),3)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '3H',

If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),4)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '2L',

If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),5)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '1L'))))), '')

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Sat, 10 Feb 2018 19:28:06 GMT

Hi Anil,

The above expression did not work for me.

I have attached the QVW and the (Example of Excel Sheet).

Can you please advise me related to this?

Kind regards,

Farrukh

Re: Second Max Value in Column Range (Straight Table)

Anil_Babu_Samineni — Sat, 10 Feb 2018 19:47:52 GMT

Will test tomorrow, For a while can you set this? This will work until unless condition not fulfill

If(Sum(CDC1)>0,

If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),1)=Sum(CDC1), Sum(CDC1), 0),

RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '1H',

If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),2)=Sum(CDC1), Sum(CDC1), 0),

RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '2H',

If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),3)=Sum(CDC1), Sum(CDC1), 0),

RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '3H',

If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),4)=Sum(CDC1), Sum(CDC1), 0),

RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '2L',

If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),5)=Sum(CDC1), Sum(CDC1), 0),

RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '1L'))))), '')

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Tue, 13 Feb 2018 18:13:29 GMT

Hi Anil,

Hi have edited the above expression but still did not get luck.

I highlighted below expression where I think I am having an issue. Please see the previously attached qvw file where I have but expression for CDF1,2.... and further.

Please help me out for this. Kind regards, Farrukh

=
If(CDC1>0,

If(IsNum(Index(RangeSum(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
), Match(If(RangeMax(RangeSum(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
),1)=Sum(Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL)))), Sum(Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL)))), 0),

RangeSum(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
), 0)))=True(), '1H',0),0)

Re: Second Max Value in Column Range (Straight Table)

settu_periasamy — Tue, 13 Feb 2018 18:49:08 GMT

Hi Farrukh,

Which field needs to look? CDF1 to CDF15 or Select_F1 to Select_F5?

Can you download the chart in excel (from attached QV) and give the expected output with some colors?

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Tue, 13 Feb 2018 18:57:14 GMT

Hi Settu,

Please see the attached QV file and Excel Sheet, (Excel Sheet has correct formula and I am trying to do the same result in Qlikview)

=
If(CDC3>0,

If(IsNum(Index(ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
), Match(If(Max(ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
),1)=Sum(Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL)))), Sum(Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL)))), 0),

ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
), 0)))=True(), '1H',0),0)

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Tue, 13 Feb 2018 18:58:50 GMT

Its a Select_F1 is the field needs to fix.

Farrukh

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Wed, 14 Feb 2018 09:47:34 GMT

Yes Settu.

Please help me out for this. Kind regards, Farrukh

Re: Second Max Value in Column Range (Straight Table)

settu_periasamy — Wed, 14 Feb 2018 14:49:31 GMT

I tried this, but not getting the expected output. I will look into this again.

Re: Second Max Value in Column Range (Straight Table)

Anil_Babu_Samineni — Wed, 14 Feb 2018 15:05:13 GMT

Does this correct?

If(CDC1>0,
If(IsNum(Index(RangeSum(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))

Re: Second Max Value in Column Range (Straight Table)

Anonymous — Wed, 14 Feb 2018 15:12:55 GMT

Hi Anil,

Please see the attached QV file and Excel Sheet, (Excel Sheet has correct formula and I am trying to do the same result in Qlikview)

The column Im tying to fix is "Select_F1" as shown in Excel file.

In QVW File CDF1 is a cumulative sum of CDC1. the logic im trying to build is if CDC1 > 0 then find the value as per (CDF1, CDF2, CDF3, CDF4 and CDF5) as shown in below table.

CDF1	CDF2	CDF3	CDF4	CDF5	Select_F1	Select_F2	Select_F3	Select_F4	Select_F5
0	0	2	0	0			First Highest
1	5	3	4	2	Lowest	First Highest	Third Highest	Second Highest	2nd Lowest
1	0	2	0	0	Second Highest		First Highest

Currently using the below expression.

=
If(CDC1>0,

If(IsNum(Index(ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
), Match(If(Max(ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
),1)=Sum(Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL)))), Sum(Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL)))), 0),

ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
), 0)))=True(), '1H',0),0)