Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bharatkishore
Creator III
Creator III

Rank in script

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

T.PNG

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

13 Replies
timpoismans
Specialist
Specialist

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
Creator III
Creator III
Author

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
Specialist
Specialist

What happens when you get the following:

     

EquipmentPart GroupKey_EMSCalendarAge_Final
E1P1Key12013-0110
E1P1Key22013-015
E1P1Key32013-0110
E1P1Key42013-013
E1P1Key52013-01

3

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

bharatkishore
Creator III
Creator III
Author

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
Specialist
Specialist

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?

     

EquipmentPart GroupKey_EMSCalendarAge_Final
E1P1Key12013-0110
E1P1Key22013-015
E1P1Key32013-0110
E1P1Key42013-013
E1P1Key52013-013
E1P1Key62013-013

Which rows would you want to keep then?

bharatkishore
Creator III
Creator III
Author

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

EquipmentPart GroupKey_EMSCalendarAge_Final
E1P1Key52013-013
E1P1Key62013-013

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

timpoismans
Specialist
Specialist

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:

Min_1.PNG

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

bharatkishore
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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:

 

EquipmentPart GroupKey_EMSCalendarAge_FinalQuantity
E1P1Key22013-0130
E1P1Key42013-0151
E2P1Key72013-01221
E2P1Key82013-01150
E3P2Key102013-0251
E3P2Key112013-0250

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.