topic Re: minimum value form a field in QlikView

minimum value form a field

kumarkp412 — Thu, 25 Jun 2015 08:01:51 GMT

Hi all,

I am having problem to calculate the minimum value of the field .For a particular date and Ref id i want to take the minimum to display it to all other rows.

My table is

Date	Ref id	id	Rank
9/302013	1	100	2
9/302013	1	212	3
9/302013	1	225	3
10/2/2013	1	400	4

my resultant table:

Date	Ref id	id	Rank
9/30/2013	1	100	2
9/30/2013	1	212	2
9/30/2013	1	225	2
10/2/2013	1	400	4

here , i need to calculate the minimum rank based on date and Ref id but i am not able to calculate .

can any one give me the solution to this.

Thank in Advance

Regards

Kumar

Re: minimum value form a field

morganaaron — Thu, 25 Jun 2015 08:10:27 GMT

Min(Total<Date, [Ref id]> Rank) should do it.

Re: minimum value form a field

PrashantSangle — Thu, 25 Jun 2015 08:13:20 GMT

Hi,

look at firstsortedvalue()

Is it based on Date and Ref id or id???

because in above example Date and Ref Id are same for 1 st 3 id

Regards

Re: minimum value form a field

kumarkp412 — Thu, 25 Jun 2015 09:12:56 GMT

Hi Aaron Morgan ,

Thanks for reply , But i want to use this in the script side

Can you give any example of this

Regards

Kumar

Re: minimum value form a field

kumarkp412 — Thu, 25 Jun 2015 09:13:49 GMT

HI Max,

It is based on the Date

Regards

Re: minimum value form a field

morganaaron — Thu, 25 Jun 2015 09:24:27 GMT

Just create a resident load like:

Join([Your Table])
Load
Date,
Min(Rank) as MinRank
Resident [Your table]

Group by Date;

Re: minimum value form a field

kumarkp412 — Thu, 25 Jun 2015 09:36:37 GMT

But , when i am using this i may get the minimum value of the rank and it is assign to all the other.

for example if it have rank values 1,2,3,4,5 and it will give the minimum of these and it is displaying .

I didnt want to show least minimum to all other , i want to take the minimum rank in a particular day(i.e 2,4,5 then i want to show 2 to all others)

Regards

kumar

Re: minimum value form a field

morganaaron — Thu, 25 Jun 2015 10:10:50 GMT

Using the above method I got exactly what you wanted from your output table - it attaches it to the date only (you can include ref id as a group by, but in response to another question you said you only want it by date) therefore will show the minimum value for the date - there is no id attached unless you put that in the resident load.

See attached - I haven't bothered adding an aggregation to MinRank because you've already done it in the script. If I've misunderstood your requirement please be clearer or show a better example result table

Re: minimum value form a field

CELAMBARASAN — Thu, 25 Jun 2015 10:18:50 GMT

Join([Your Table])
Load
Date,

[Ref id],
Min(Rank) as MinRank
Resident [Your table]

Group by Date, [Ref id];

Re: minimum value form a field

kumarkp412 — Thu, 25 Jun 2015 13:56:10 GMT

Thanks Aaron ,

It was helped me a lot .

Regards

Kumar