topic Re: Get Data with Max Value for each ID in QlikView

Get Data with Max Value for each ID

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need a solution for my problem.

I have raw data like this:

The result i expect is:

So, i want to get the record that has max Total_TRX value for each ID

Is it possible to make it with QlikView load script ?

Really appreciate your help.

Thanks in advance.

Anil_Babu_Samineni — Fri, 17 Mar 2017 03:01:09 GMT

I really don't why do you need this? Sometime, They want whole data then what you do. Anyway, for your question answer should this

Welcome:

LOAD * Inline [

ID, Name, Total_TRX

1, A, 10

1, B, 20

2, C, 20

3, D, 50

3, E, 1

];

NoConcatenate

MainTable:

LOAD

ID,

MAX(Total_TRX) as Total_TRX

Resident Welcome Group By ID Order By ID;

Left Join

LOAD

ID,

Total_TRX,

Name

Resident Welcome;

Drop Table Welcome;

aapurva09 — Fri, 17 Mar 2017 07:42:05 GMT

Hi,

Using 'Group by ID' in the script will give you the expected results.

Anonymous — Fri, 17 Mar 2017 08:36:22 GMT

Hi Anil,

Thanks for the solution, it solved my problem perfectly