topic Re: Rank in script in QlikView

Rank in script

bharatkishore — Mon, 24 Sep 2018 10:55:38 GMT

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:

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

Re: Rank in script

timpoismans — Mon, 24 Sep 2018 11:00:24 GMT

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.

Re: Rank in script

bharatkishore — Mon, 24 Sep 2018 11:04:21 GMT

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.

Re: Rank in script

timpoismans — Mon, 24 Sep 2018 11:15:51 GMT

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?

Re: Rank in script

bharatkishore — Mon, 24 Sep 2018 11:19:03 GMT

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.

Re: Rank in script

timpoismans — Mon, 24 Sep 2018 11:32:36 GMT

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?

Re: Rank in script

bharatkishore — Mon, 24 Sep 2018 11:39:04 GMT

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

Re: Rank in script

timpoismans — Mon, 24 Sep 2018 11:51:29 GMT

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.

Re: Rank in script

bharatkishore — Mon, 24 Sep 2018 12:03:03 GMT

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

Re: Rank in script

bharatkishore — Mon, 24 Sep 2018 12:30:02 GMT

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.

Re: Rank in script

timpoismans — Mon, 24 Sep 2018 13:04:24 GMT

You're making groups based on the values of your dimensions.

Why does this have to happen in the script?

Re: Rank in script

bharatkishore — Mon, 24 Sep 2018 13:12:48 GMT

Because i.e one the requirement and based on the the above logic will be deriving other steps in the script...

Re: Rank in script

timpoismans — Mon, 24 Sep 2018 13:24:41 GMT

I don't think it's possible to load two rows where [Age_Final] is min. per unique value of your Equipment field

If you'd tell us what your goal is, perhaps we could help you further.

Re: Rank in script

bharatkishore — Mon, 24 Sep 2018 13:30:55 GMT

I need to get the min age values which are 2 min age values. For every record i mean for every equipment and Part Group and Calendar month.

In sql it has been developed with help of rank function there it has been considered as min of 2 ranks,

Hope you can understand the scenario

The same can we do in qlikview.

Please let me know if you need anything more.