topic Set Analysis: Count only latest record per Player and Skill in App Development https://community.qlik.com/t5/App-Development/Set-Analysis-Count-only-latest-record-per-Player-and-Skill/m-p/1826009#M67914 <P>Hi,&nbsp;</P><P>I have a raw table as shown below, where I have "Players" that get assessed on "Skills" multiple times.</P><TABLE><TBODY><TR><TD>Player</TD><TD>Skill</TD><TD>Rating</TD><TD>Category</TD><TD>Assessment Date</TD></TR><TR><TD>Lionel Messi</TD><TD>Dribbling</TD><TD>3.5</TD><TD>3-4</TD><TD>1/1/2021</TD></TR><TR><TD><STRONG><FONT color="#008000">Lionel Messi</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">Dribbling</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">4</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">3-4</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">5/1/2021</FONT></STRONG></TD></TR><TR><TD>Lionel Messi</TD><TD>Free Shots</TD><TD>3.5</TD><TD>3-4</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#008000"><STRONG>Lionel Messi</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>Free Shots</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3.9</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3-4</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>5/1/2021</STRONG></FONT></TD></TR><TR><TD>Luis Suarez</TD><TD>Dribbling</TD><TD>2</TD><TD>2-3</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#008000"><STRONG>Luis Suarez</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>Dribbling</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>2.8</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>2-3</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>5/1/2021</STRONG></FONT></TD></TR><TR><TD>Luis Suarez</TD><TD>Free Shots</TD><TD>3</TD><TD>3-4</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#008000"><STRONG>Luis Suarez</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>Free Shots</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3.5</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3-4</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>5/1/2021</STRONG></FONT></TD></TR><TR><TD>Pedri</TD><TD>Dribbling</TD><TD>2.2</TD><TD>2-3</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#008000"><STRONG>Pedri</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>Dribbling</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3.1</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3-4</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>5/1/2021</STRONG></FONT></TD></TR><TR><TD>Pedri</TD><TD>Free Shots</TD><TD>1</TD><TD>1-2</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#008000"><STRONG>Pedri</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>Free Shots</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>1.6</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>1-2</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>5/1/2021</STRONG></FONT></TD></TR><TR><TD>Ansu Fati</TD><TD>Dribbling</TD><TD>2.1</TD><TD>2-3</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#FF0000"><STRONG>Ansu Fati</STRONG></FONT></TD><TD><FONT color="#FF0000"><STRONG>Dribbling</STRONG></FONT></TD><TD><FONT color="#FF0000"><STRONG>2.6</STRONG></FONT></TD><TD><FONT color="#FF0000"><STRONG>2-3</STRONG></FONT></TD><TD><FONT color="#FF0000"><STRONG>6/2/2021</STRONG></FONT></TD></TR><TR><TD>Ansu Fati</TD><TD>Free Shots</TD><TD>1</TD><TD>1-2</TD><TD>1/1/2021</TD></TR><TR><TD><STRONG><FONT color="#008000">Ansu Fati</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">Free Shots</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">1.2</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">1-2</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">5/1/2021</FONT></STRONG></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>My output should be a table with skills, and counting number of players on each category, then I need to consider the Rating obtained on the last assessment only. The assessment date might differ between players also, so using Max(date) didn't work as it takes only the max date of the whole table and will get nulls on players that do not have that date.&nbsp;</P><TABLE><TBODY><TR><TD>Skill</TD><TD>0-1</TD><TD>1-2</TD><TD>2-3</TD><TD>3-4</TD></TR><TR><TD>Dribbling</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2</TD><TD>2</TD></TR><TR><TD>Free Shots</TD><TD>&nbsp;</TD><TD>2</TD><TD>&nbsp;</TD><TD>2</TD></TR></TBODY></TABLE><P>&nbsp;</P><DIV><DIV>For dribbling, level 3-4 it counts 2 players (Messi and Pedri both assessed on the same date)</DIV><DIV>*** For dribbling, level 2-3 it counts 2 players (Luis Suarez on Jan 5th, and <STRONG><SPAN>Ansu Fati on Feb 6th....)</SPAN></STRONG></DIV><DIV><STRONG>etc etc...</STRONG></DIV></DIV><P>&nbsp;</P><P>Any idea of a workaround for this? - I don't want to loose the historical data though so I can build also later evolution charts...</P> Wed, 22 Dec 2021 15:42:54 GMT Igor_Torrealba 2021-12-22T15:42:54Z Set Analysis: Count only latest record per Player and Skill https://community.qlik.com/t5/App-Development/Set-Analysis-Count-only-latest-record-per-Player-and-Skill/m-p/1826009#M67914 <P>Hi,&nbsp;</P><P>I have a raw table as shown below, where I have "Players" that get assessed on "Skills" multiple times.</P><TABLE><TBODY><TR><TD>Player</TD><TD>Skill</TD><TD>Rating</TD><TD>Category</TD><TD>Assessment Date</TD></TR><TR><TD>Lionel Messi</TD><TD>Dribbling</TD><TD>3.5</TD><TD>3-4</TD><TD>1/1/2021</TD></TR><TR><TD><STRONG><FONT color="#008000">Lionel Messi</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">Dribbling</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">4</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">3-4</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">5/1/2021</FONT></STRONG></TD></TR><TR><TD>Lionel Messi</TD><TD>Free Shots</TD><TD>3.5</TD><TD>3-4</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#008000"><STRONG>Lionel Messi</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>Free Shots</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3.9</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3-4</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>5/1/2021</STRONG></FONT></TD></TR><TR><TD>Luis Suarez</TD><TD>Dribbling</TD><TD>2</TD><TD>2-3</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#008000"><STRONG>Luis Suarez</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>Dribbling</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>2.8</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>2-3</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>5/1/2021</STRONG></FONT></TD></TR><TR><TD>Luis Suarez</TD><TD>Free Shots</TD><TD>3</TD><TD>3-4</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#008000"><STRONG>Luis Suarez</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>Free Shots</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3.5</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3-4</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>5/1/2021</STRONG></FONT></TD></TR><TR><TD>Pedri</TD><TD>Dribbling</TD><TD>2.2</TD><TD>2-3</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#008000"><STRONG>Pedri</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>Dribbling</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3.1</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>3-4</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>5/1/2021</STRONG></FONT></TD></TR><TR><TD>Pedri</TD><TD>Free Shots</TD><TD>1</TD><TD>1-2</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#008000"><STRONG>Pedri</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>Free Shots</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>1.6</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>1-2</STRONG></FONT></TD><TD><FONT color="#008000"><STRONG>5/1/2021</STRONG></FONT></TD></TR><TR><TD>Ansu Fati</TD><TD>Dribbling</TD><TD>2.1</TD><TD>2-3</TD><TD>1/1/2021</TD></TR><TR><TD><FONT color="#FF0000"><STRONG>Ansu Fati</STRONG></FONT></TD><TD><FONT color="#FF0000"><STRONG>Dribbling</STRONG></FONT></TD><TD><FONT color="#FF0000"><STRONG>2.6</STRONG></FONT></TD><TD><FONT color="#FF0000"><STRONG>2-3</STRONG></FONT></TD><TD><FONT color="#FF0000"><STRONG>6/2/2021</STRONG></FONT></TD></TR><TR><TD>Ansu Fati</TD><TD>Free Shots</TD><TD>1</TD><TD>1-2</TD><TD>1/1/2021</TD></TR><TR><TD><STRONG><FONT color="#008000">Ansu Fati</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">Free Shots</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">1.2</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">1-2</FONT></STRONG></TD><TD><STRONG><FONT color="#008000">5/1/2021</FONT></STRONG></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>My output should be a table with skills, and counting number of players on each category, then I need to consider the Rating obtained on the last assessment only. The assessment date might differ between players also, so using Max(date) didn't work as it takes only the max date of the whole table and will get nulls on players that do not have that date.&nbsp;</P><TABLE><TBODY><TR><TD>Skill</TD><TD>0-1</TD><TD>1-2</TD><TD>2-3</TD><TD>3-4</TD></TR><TR><TD>Dribbling</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2</TD><TD>2</TD></TR><TR><TD>Free Shots</TD><TD>&nbsp;</TD><TD>2</TD><TD>&nbsp;</TD><TD>2</TD></TR></TBODY></TABLE><P>&nbsp;</P><DIV><DIV>For dribbling, level 3-4 it counts 2 players (Messi and Pedri both assessed on the same date)</DIV><DIV>*** For dribbling, level 2-3 it counts 2 players (Luis Suarez on Jan 5th, and <STRONG><SPAN>Ansu Fati on Feb 6th....)</SPAN></STRONG></DIV><DIV><STRONG>etc etc...</STRONG></DIV></DIV><P>&nbsp;</P><P>Any idea of a workaround for this? - I don't want to loose the historical data though so I can build also later evolution charts...</P> Wed, 22 Dec 2021 15:42:54 GMT https://community.qlik.com/t5/App-Development/Set-Analysis-Count-only-latest-record-per-Player-and-Skill/m-p/1826009#M67914 Igor_Torrealba 2021-12-22T15:42:54Z