Rank in script

[bharatkishore]

HI All,

I have  the following columns in script:

     Equipment,

     [Part Group],

     Key_EMS,

     Calendar,

     Age_Final

now in Age_Final i have the following output: and image for reference:

10

5

10

3

Now i need to get only min ages i.e. 3 and 5. I need to this in script. Do i need to use Rank(Guess it cannot be worked in script) or autonumber to get only min 2 ages.

Attaching qvw and excel for reference.

Please let me know how to do this.

Thanks,

Bharat

[timpoismans]

Please clarify your issue a bit more:

• You want the two lowest age values? Three? Four? I presume we only get 3 and 5 here because of the e.g.

[bharatkishore]

HI Tim,

Thanks for your reply.

Yes  i need to get 3 and 5.. i mean lowest values(any numbers it can be but needed only lowest two values).

Please let me know if you need anything more.

[timpoismans]

What happens when you get the following:

Equipment	Part Group	Key_EMS	Calendar	Age_Final
E1	P1	Key1	2013-01	10
E1	P1	Key2	2013-01	5
E1	P1	Key3	2013-01	10
E1	P1	Key4	2013-01	3
E1	P1	Key5	2013-01	3

Which rows do you want to see? Key2, Key4 and Key5?

[bharatkishore]

Then i should get key4  and key5 which is 3,3 (bec  i need minimum 2 numbers of age.)

Please let me know if you need anything more.

[timpoismans]

Even though it's the same value?

I'd think you'd want to see the two lowest ages, which are 3 and 5 and see all rows associated with those rows, so the row with Key2, Key4 and Key5.

What would you do if you have three rows with 3 as the value for Age_Final?

Equipment	Part Group	Key_EMS	Calendar	Age_Final
E1	P1	Key1	2013-01	10
E1	P1	Key2	2013-01	5
E1	P1	Key3	2013-01	10
E1	P1	Key4	2013-01	3
E1	P1	Key5	2013-01	3
E1	P1	Key6	2013-01	3

Which rows would you want to keep then?

[bharatkishore]

In the above mentioned one i would consider any of the two rows in Key4 , key5 , key6..

In Key4,Key5,Key6 i can get any rows because the minimum value is 3 and the output should be

Equipment	Part Group	Key_EMS	Calendar	Age_Final
E1	P1	Key5	2013-01	3
E1	P1	Key6	2013-01	3

It can be any two rows i mean either Key4,Key 6 or Key4,Key5 any two...

[timpoismans]

If I use the following code on the table from my previous post:

Pre_Min:

LOAD

    Equipment,

    "Part Group",

    Key_EMS,

    Calendar,

    Age_Final

FROM [lib://Community/min.xlsx]

(ooxml, embedded labels, table is Sheet1);

Min:

NoConcatenate

First 2 Load

*

Resident Pre_Min

Order By Age_Final asc;

Drop Table Pre_Min;

I get the following result:

You lose any 3rd, 4th, 5th,... row with the same value though, but if that's ok, this should solve your problem.

[bharatkishore]

Thanks for your reply..

Will try and let you know if it works..

As per your image that is what i need..Will test with my original data..

[bharatkishore]

HI Tim,

For wherever i have 1 equipment it is working fine, Kindly find the attached excel.

Now here the i have added  quantity column and some more equipments and part groups and keys and the output should be:

Equipment	Part Group	Key_EMS	Calendar	Age_Final	Quantity
E1	P1	Key2	2013-01	3	0
E1	P1	Key4	2013-01	5	1
E2	P1	Key7	2013-01	22	1
E2	P1	Key8	2013-01	15	0
E3	P2	Key10	2013-02	5	1
E3	P2	Key11	2013-02	5	0

And need to do sum of quantity and  group by should be on following columns i.e Equipment,Part Group,Calendar

Please let me know if you need anything more.