Get Data with Max Value for each ID

[Anonymous]

Hi,

I need a solution for my problem.

I have raw data like this:

ID	Name	Total_TRX
1	A	10
1	B	20
2	C	20
3	D	50
3	E	1

The result i expect is:

ID	Name	Total_TRX
1	B	20
2	C	20
3	D	50

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 · Accepted solution]

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]

Hi,

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

[Anonymous]

Hi Anil,

Thanks for the solution, it solved my problem perfectly