How to calculate second-highest data value

[sreesuku2]

Hi Team,

I want to create calculated column 'sec'- as per the below file format. I have columns Prim, Oth1 to Oth6 in my actual input file.

Prim- is the highest value among columns Oth1 to Oth 6.

Sec - is the second highest value among columns Oth1 to Oth6. But If there are columns with same highest values, then prim and sec are the same.

Eg : In the case of ser# 00191J, Prim is 2 sec is 1.
For 546720 Prim is 4 sec is also 4. Since Oth1 is having value 4 and Oth2 is also having value 4.
For 04864F Prim is 3 sec is 0

Is there any way I create this sec column ?

Code: Select all

Ser #	Prim	Oth1	Oth2	Oth3	Oth4	Oth5	Oth6	Sec
00191J	2	   2	   1	   0	   1	   0	   0	   1
546720	4	   4	   4	   3	   0	   0	   0	   4
04864F	3	   0	   3	   0	   0	   0	   0	   0
2121AC	3	   3	   3	   2	   2	   0	   0	   2
04974N	0	   0	   0	   0	   0	   0	   0	   0

Note : I think the format of the file is coming in distorted way. Is there any way I can attach the file or give a screen shot

Regards
Sree

[chulett]

While you could include a screen shot using the [Img] tags after you've uploaded it to a file sharing site, as you can see there's no need. The forum software automatically removes 'extra' whitespace which is what was affecting your example. I wrapped it in

Code: Select all

[/b] tags which preserve whitespace but that isn't the end of the story. I still had to use the 'Preview' option to see what the post [i]was going to look like[/i] and then adjust the spacing until everything lined up - these are proportional fonts so what you type is not exactly how it goes up when posted. Several tweaks later and the result is what you see.

[sreesuku2]

Hi Craig,

Thanks for all your technical work
However I am waiting for the required solution for others

If you can shed some light on that, it will be great

[asorrell]

Since this is a server job, you could do it in a routine. Pass the "Oth" values to the routine as arguments. Then sort the data values and return the second highest one.

[ray.wurlod]

You could use a stage variable for each Oth column testing whether the value is less than Prim and higher than anything already encountered. But a routine would, in my opinion, be easier to maintain.