Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

get respective record of max value - using group by

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

    

1 Solution

Accepted Solutions

Re: get respective record of max value - using group by

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

Straighttab.png

Regards

4 Replies
sibideepak
Contributor II

Re: get respective record of max value - using group by

Hi ,

For id = 1,

You will not get Accepted ,you will get Rejected only

And You can approach by Good UI

Not applicable

Re: get respective record of max value - using group by

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

Re: get respective record of max value - using group by

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

Straighttab.png

Regards

Not applicable

Re: get respective record of max value - using group by

Thanks anand,

Community Browser