topic How to pick most recent record in QlikView https://community.qlik.com/t5/QlikView/How-to-pick-most-recent-record/m-p/516395#M691585 <HTML><HEAD></HEAD><BODY><P><BR />Hi</P><P>I have a scenario where an order can have multiple certificates, but in the result set I wish to see most recent cert corresponding to the order based on start_date. Please see data below for 2 orders, I should be able to pick the highlighted data.</P><P></P><TABLE border="1" class="jiveBorder" style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; WIDTH: 100%; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG>Order_ID</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG>CERT_ID</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG>Start_date</STRONG></TH></TR><TR><TD style="padding: 2px;">12345</TD><TD style="padding: 2px;">567</TD><TD style="padding: 2px;">09/01/2013</TD></TR><TR><TD style="padding: 2px;">12345</TD><TD style="padding: 2px;">568</TD><TD style="padding: 2px;">09/02/2013</TD></TR><TR><TD style="padding: 2px;"><SPAN style="color: #00ff00;">12345</SPAN></TD><TD style="padding: 2px;"><SPAN style="color: #00ff00;">569</SPAN></TD><TD style="padding: 2px;"><SPAN style="color: #00ff00;">09/03/2013</SPAN></TD></TR><TR><TD style="padding: 2px;">56789</TD><TD style="padding: 2px;">678</TD><TD style="padding: 2px;">09/07/2013</TD></TR><TR><TD style="padding: 2px;"><SPAN style="color: #00ff00;">56789</SPAN></TD><TD style="padding: 2px;"><SPAN style="color: #00ff00;">675</SPAN></TD><TD style="padding: 2px;"><SPAN style="color: #00ff00;">09/07/2013</SPAN></TD></TR><TR><TD style="padding: 2px;"></TD><TD style="padding: 2px;"></TD><TD style="padding: 2px;"></TD></TR></TBODY></TABLE><P> Please help. Thank you Much in advance</P></BODY></HTML> Thu, 12 Sep 2013 17:35:22 GMT 2013-09-12T17:35:22Z How to pick most recent record https://community.qlik.com/t5/QlikView/How-to-pick-most-recent-record/m-p/516395#M691585 <HTML><HEAD></HEAD><BODY><P><BR />Hi</P><P>I have a scenario where an order can have multiple certificates, but in the result set I wish to see most recent cert corresponding to the order based on start_date. Please see data below for 2 orders, I should be able to pick the highlighted data.</P><P></P><TABLE border="1" class="jiveBorder" style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; WIDTH: 100%; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG>Order_ID</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG>CERT_ID</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;"><STRONG>Start_date</STRONG></TH></TR><TR><TD style="padding: 2px;">12345</TD><TD style="padding: 2px;">567</TD><TD style="padding: 2px;">09/01/2013</TD></TR><TR><TD style="padding: 2px;">12345</TD><TD style="padding: 2px;">568</TD><TD style="padding: 2px;">09/02/2013</TD></TR><TR><TD style="padding: 2px;"><SPAN style="color: #00ff00;">12345</SPAN></TD><TD style="padding: 2px;"><SPAN style="color: #00ff00;">569</SPAN></TD><TD style="padding: 2px;"><SPAN style="color: #00ff00;">09/03/2013</SPAN></TD></TR><TR><TD style="padding: 2px;">56789</TD><TD style="padding: 2px;">678</TD><TD style="padding: 2px;">09/07/2013</TD></TR><TR><TD style="padding: 2px;"><SPAN style="color: #00ff00;">56789</SPAN></TD><TD style="padding: 2px;"><SPAN style="color: #00ff00;">675</SPAN></TD><TD style="padding: 2px;"><SPAN style="color: #00ff00;">09/07/2013</SPAN></TD></TR><TR><TD style="padding: 2px;"></TD><TD style="padding: 2px;"></TD><TD style="padding: 2px;"></TD></TR></TBODY></TABLE><P> Please help. Thank you Much in advance</P></BODY></HTML> Thu, 12 Sep 2013 17:35:22 GMT https://community.qlik.com/t5/QlikView/How-to-pick-most-recent-record/m-p/516395#M691585 2013-09-12T17:35:22Z Re: How to pick most recent record https://community.qlik.com/t5/QlikView/How-to-pick-most-recent-record/m-p/516396#M691586 <HTML><HEAD></HEAD><BODY><P>Create a chart with dimension Order_ID and as expression</P><P></P><P>=FirstSortedValue( CERT_ID, -Start_date)</P><P></P><P>where start date must have a numeric representation (i.e. like a QV date as a dual value).</P></BODY></HTML> Thu, 12 Sep 2013 17:37:58 GMT https://community.qlik.com/t5/QlikView/How-to-pick-most-recent-record/m-p/516396#M691586 swuehl 2013-09-12T17:37:58Z Re: How to pick most recent record https://community.qlik.com/t5/QlikView/How-to-pick-most-recent-record/m-p/516397#M691587 <HTML><HEAD></HEAD><BODY><P>Hi</P><P></P><P>Try with <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;">FirstSortedValue()</SPAN></P><P></P><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;">FirstSortedValue( CERT_ID, -Start_date)</SPAN></P></BODY></HTML> Thu, 12 Sep 2013 17:41:34 GMT https://community.qlik.com/t5/QlikView/How-to-pick-most-recent-record/m-p/516397#M691587 MayilVahanan 2013-09-12T17:41:34Z