Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with getting the best rank for the year

Hi,

I have got two sets of data one containing Year & Activity & the other containing Contribution of student of various Schools in various methods.

Eg

Table1:

Year Activity

2006 ABC

2007 ABC

Table2:

School Student ContributionMethod Contribution YearA

A Jack Cash 100 2006

A Jack Kind 105 2006

A Jack Cash 110 2007

A Jack Card 150 2007

Similar data for other schools & other student as well

The two Year Variables can not be linked and have to be kept seperate .What I want as a solution is a table where when Activity ABC of Year1 is selected the highest contribution of Jack for the year is displayed

For example:

In Row 1 where the year is 2006 - Jack's highest contribution ie 105 is shown & in Row 2 where the year is 2007 - Jack's highest contribution oe 150 is shown

Please help ......

1 Solution

Accepted Solutions
Not applicable
Author

13 Replies
tresesco
MVP
MVP

Hi Umang,

Try something like this:

LOAD

Max(Contribution) as MaxContribution, Year, Student

FROM ..... GROUP BY Student,Year ;

Regards, tresesco

Not applicable
Author

Hi Tresco,

Could you possibly attach a example QV highlighting how the same can be done I am attaching a excel with sample data.

Not applicable
Author

Something like this?

Not applicable
Author

Jochem in this case you have joined the two tables with "Year" the two tables can not be not joined with the variable "Year"

Not applicable
Author

How about this? Not linked, and used set analysis!

tresesco
MVP
MVP

Hi Umang,

Find the application attached.

Regards, tresesco

Not applicable
Author

I think I was not able to explain properly, Year fields can't be joined directly But the tables can be linked indirectly

In the excel I've added another sheet which links the activities to the students - so all tables are linked Now no further linking must be done and the solution remains the same

Not applicable
Author

SEE MY PREVIOUS POST, THIS ONE IS WITHOUT THE LINKING!

Not applicable
Author

Again the file (NO LINKING ON YEAR)