Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor 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
Highlighted
Valued Contributor

Re: Rank in script

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.
Highlighted
Contributor III

Re: Rank in script

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.

Highlighted
Valued Contributor

Re: Rank in script

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?

Highlighted
Contributor III

Re: Rank in script

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.

Highlighted
Valued Contributor

Re: Rank in script

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?

Highlighted
Contributor III

Re: Rank in script

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

Highlighted
Valued Contributor

Re: Rank in script

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.

Highlighted
Contributor III

Re: Rank in script

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

Highlighted
Contributor III

Re: Rank in script

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.