topic Re: Max identifier in QlikView

Max identifier

keithlawrence — Mon, 27 Jul 2015 13:31:55 GMT

I'm trying to show the maximum occupancy of a building in a table. This is my data:

Building Number, Size, Occupant

1, 10, A

1, 8, B

1, 6, C

2, 5, A

2, 6, B

3, 10, A

3, 6, B

3, 8 C

I have table grouped by Building Number and I want to show the largest occupier of that building. I want to see this:

Building, Largest tenant

1, A

2, B

3, A

I have a syntax that works out the largest tenant but it only populates when I have one building selected. I can't get them all to be shown in a table for some reason.

Any help?

Re: Max identifier

sunny_talwar — Mon, 27 Jul 2015 13:36:38 GMT

Try this:

Dimension: [Building Number]

Expression: =FirstSortedValue(Occupant, -Size)

Output

Re: Max identifier

keithlawrence — Mon, 27 Jul 2015 13:43:49 GMT

Works a dream! Thanks Sunny

Re: Max identifier

sunny_talwar — Mon, 27 Jul 2015 13:47:30 GMT

No problem. Here is another way you can do it, just in case you find this more useful.

Script (creating flags)

Table:

LOAD * Inline [

Building Number, Size, Occupant

1, 10, A

1, 8, B

1, 6, C

2, 5, A

2, 6, B

3, 10, A

3, 6, B

3, 8, C

];

Join(Table)

LOAD [Building Number],

Max(Size) as Size,

'1' as Flag

Resident Table

Group By [Building Number];

Table Box

Straight Table

Dimension: [Building Number]

Expression: =Only({<Flag = {1}>}Occupant)

Best,

Sunny

Re: Max identifier

sasiparupudi1 — Mon, 27 Jul 2015 13:51:05 GMT

load * Inline

[

Building Number, Size, Occupant

1, 10, A

1, 8, B

1, 6, C

2, 5, A

2, 6, B

3, 10, A

3, 6, B

3, 8, C

];

MAP:

Mapping LOAD

[Building Number] & Size as MapKey,Occupant

Resident x;

x1:

NoConcatenate load [Building Number] , max(Size) as Size,ApplyMap('MAP',[Building Number]&max(Size),'N/A') as Occupant

Resident x

Group by [Building Number];

drop Table x;

Re: Max identifier

Kushal_Chawda — Mon, 27 Jul 2015 14:12:53 GMT

hi sunindia‌ ,

when there is same size of Two Occupant , Firstsorted value will not work.

then need to follow another way.

Re: Max identifier

sunny_talwar — Mon, 27 Jul 2015 14:15:41 GMT

Kush you are right, but that is assuming that Keith would want to see both of them. I actually provided the response in the context of things. Making assumptions can complicate matters

Best,

Sunny

Re: Max identifier

sunny_talwar — Mon, 27 Jul 2015 14:16:58 GMT

Also if you look at the my second response, that should take care of two max values

Re: Max identifier