get respective record of max value - using group by

[#1]

i have two tables

table1:

load * inline

[srno, id , status

101,1,Accepted

102,1,Verified

103,1,Rejected

104,2,Accepted

105,2,Verified

106,3,Accepted

107,4,Accepted

108,4,Verified];

table2:

load * inline

[id, product , quantity

1,A,10

2,B,20

3,C,30

4,D,40

];

expected result

id,     status     product      quantity

1       Accepted     A          10

2     Verified     B          20

3     Accepted     C          30

4     Verified     D          40

(result can be viewable in table chart)

means i need respective status according to the max(srno) and group by id

How can i approach the above scenario either by UI or by script

[sibideepak]

Hi ,

For id = 1,

You will not get Accepted ,you will get Rejected only

And You can approach by Good UI

[#3]

sorry for typo.... u are right ,.. rejected is true for id=1

[its_anandrjs · Accepted solution]

Please make correction for first it is rejected  not Accepted and in the load script side you can achieve this by finding max number and then use it. See the script for that.

Table1:

LOAD * INLINE [

    srno, id, status

    101, 1, Accepted

    102, 1, Verified

    103, 1, Rejected

    104, 2, Accepted

    105, 2, Verified

    106, 3, Accepted

    107, 4, Accepted

    108, 4, Verified

];

Table2: 

LOAD * INLINE [

    id, product, quantity

    1, A, 10

    2, B, 20

    3, C, 30

    4, D, 40

];

Final:

LOAD

id, Max(srno) as MaxSrno

Resident Table1

Group By id;

Right Join(Final)

LOAD

srno as MaxSrno, status as STATUS

Resident Table1;

Right Join(Final)

LOAD

id, product as PRODUCT, quantity AS QTY

Resident Table2;

And then in the

Dim1:- id

Dim2:- STATUS

Dim3:- PRODUCT

Expression:- Sum(QTY)

And output is

Regards

[#5]

Thanks anand,