Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Maximum Value in Script for different ID

Hi all,

I have data like

Table1:

IDValue

1

53
132
189
145
231
232
359
430
4-39
521
534
546
6-

I want

Table2:

IDValue
189
232
359
4-39
546
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

1 Solution

Accepted Solutions
PrashantSangle

Hi,

Use Group By

Regards

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

12 Replies
PrashantSangle

Hi,

Use Group By

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable
Author

Thanks Max for the quick response.

Forgot about the group by clause

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

PrashantSangle

Hi,

Please close thread, If you got the answer.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable
Author

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.

tamilarasu
Champion
Champion

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;

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

PrashantSangle

Hi,

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.