topic Re: Maximum Value in Script for different ID in QlikView

Maximum Value in Script for different ID

Wed, 30 Dec 2015 10:03:13 GMT

Hi all,

I have data like

Table1:

ID	Value
1	53
1	32
1	89
1	45
2	31
2	32
3	59
4	30
4	-39
5	21
5	34
5	46
6	-

I want

Table2:

ID	Value
1	89
2	32
3	59
4	-39
5	46
6	-

How to do this in Script?

Script written :-

Table2:

Load Distinct ID,

Max(Value) as New_Value

Resident Table1;

But its showing invalid expression on reload.

Any help?

Regards,

Anjali Gupta

Re: Maximum Value in Script for different ID

PrashantSangle — Wed, 30 Dec 2015 10:05:48 GMT

Hi,

Use Group By

Regards

Re: Maximum Value in Script for different ID

PrashantSangle — Wed, 30 Dec 2015 10:07:00 GMT

Since Max() is aggregation funtion then you have to use group by

try like

Load Distinct ID,

Max(Value) as New_Value

Resident Table1

group by ID;

for details see in help menu

Regards,

Re: Maximum Value in Script for different ID

Wed, 30 Dec 2015 10:08:06 GMT

Thanks Max for the quick response.

Forgot about the group by clause

Re: Maximum Value in Script for different ID

Peter_Cammaert — Wed, 30 Dec 2015 10:09:18 GMT

And leave the DISTINCT out. GROUP BY takes care of returning a single ID value.

Re: Maximum Value in Script for different ID

PrashantSangle — Wed, 30 Dec 2015 10:19:19 GMT

Hi,

Please close thread, If you got the answer.

Regards,

Re: Maximum Value in Script for different ID

Wed, 30 Dec 2015 10:25:59 GMT

Hi I have one more question regarding this.

Can i use

Load *,

Max(Value) as New_Value

Resident Table1

group by ID;

As i have more than one column.

Using this is showing invalid expression error again.

Re: Maximum Value in Script for different ID

tamilarasu — Wed, 30 Dec 2015 10:34:22 GMT

You need to all the fields in group by statement. Something like below,

Load Distinct ID,

Field1,

Field2,

Max(Value) as New_Value

Resident Table1

group by ID, Field1, Field2;

Re: Maximum Value in Script for different ID

Peter_Cammaert — Wed, 30 Dec 2015 10:37:54 GMT

No you cannot. For every value of ID, the GROUP BY clause will reduce possibly a lot of records to a single one and take the maximum value for field Value and store it in New_Value. But what should it do with all other possibly different field values?

All other fields have to be specified either in the GROUP BY clause or embedded in an aggregation function (like Sum, Count, Max etc.) in the column list.

FirstSortedValue is an interesting function to pick values in other fields, based on a order you choose (like a timestamp for most recent value).

Best,

Peter

Re: Maximum Value in Script for different ID

PrashantSangle — Wed, 30 Dec 2015 10:55:16 GMT

Hi,

for group by clause you have to add all those field which are not aggregated.

Regards

Re: Maximum Value in Script for different ID

Wed, 30 Dec 2015 12:20:26 GMT

Thanks Tamil Nagaraj..

Re: Maximum Value in Script for different ID

Wed, 30 Dec 2015 12:20:59 GMT

Thanks Peter for the knowledge..

Regards,

Anjali Gupta

Re: Maximum Value in Script for different ID

Wed, 30 Dec 2015 12:22:37 GMT

Thanks Max for the help.