topic Find Rank using Firstsortedvalue in QlikView https://community.qlik.com/t5/QlikView/Find-Rank-using-Firstsortedvalue/m-p/38270#M777839 <HTML><HEAD></HEAD><BODY><P>Hi Hello,</P><P></P><P>I have a data like this:</P><TABLE border="1" class="jiveBorder" style="border: 1px solid #000000; border-image: none; width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>ID</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>ID NAME</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>LAYER</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>PERIOD</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>R STATUS</STRONG></TH></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">A</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">COMP</TD></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">B</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">PEND</TD></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">DB</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">SCOPE</TD></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">D</TD><TD style="padding: 2px;">OS</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">NOTAVBLE</TD></TR><TR><TD style="padding: 2px;">1217</TD><TD style="padding: 2px;">A</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">NOTAVBLE</TD></TR><TR><TD style="padding: 2px;">1217</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">PEND</TD></TR><TR><TD style="padding: 2px;">1217</TD><TD style="padding: 2px;">D</TD><TD style="padding: 2px;">DB</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">COMP</TD></TR></TBODY></TABLE><P></P><P>If one ID has more then one R STATUS ,then we need to calculate rank based on below table.</P><P></P><P><STRONG>R STATUS, RANK:</STRONG></P><P>COMP,1</P><P>PEND,2</P><P>SCOPE,3</P><P>NOTAVBLE,4</P><P></P><P></P><P>RESULTS:</P><P></P><TABLE border="1" class="jiveBorder" style="border: 1px solid #000000; border-image: none; width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>ID</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>ID NAME</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>LAYER</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>PERIOD</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>R STATUS</STRONG></TH></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">A</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">COMP</TD></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">DB</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">SCOPE</TD></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">D</TD><TD style="padding: 2px;">OS</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">NOTAVBLE</TD></TR><TR><TD style="padding: 2px;">1217</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">PEND</TD></TR><TR><TD style="padding: 2px;">1217</TD><TD style="padding: 2px;">D</TD><TD style="padding: 2px;">DB</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">COMP</TD></TR></TBODY></TABLE><P></P><P>Thanks,</P><P>Gireesh</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT gireesh1216 2020-11-25T16:16:04Z Find Rank using Firstsortedvalue https://community.qlik.com/t5/QlikView/Find-Rank-using-Firstsortedvalue/m-p/38270#M777839 <HTML><HEAD></HEAD><BODY><P>Hi Hello,</P><P></P><P>I have a data like this:</P><TABLE border="1" class="jiveBorder" style="border: 1px solid #000000; border-image: none; width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>ID</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>ID NAME</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>LAYER</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>PERIOD</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>R STATUS</STRONG></TH></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">A</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">COMP</TD></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">B</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">PEND</TD></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">DB</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">SCOPE</TD></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">D</TD><TD style="padding: 2px;">OS</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">NOTAVBLE</TD></TR><TR><TD style="padding: 2px;">1217</TD><TD style="padding: 2px;">A</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">NOTAVBLE</TD></TR><TR><TD style="padding: 2px;">1217</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">PEND</TD></TR><TR><TD style="padding: 2px;">1217</TD><TD style="padding: 2px;">D</TD><TD style="padding: 2px;">DB</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">COMP</TD></TR></TBODY></TABLE><P></P><P>If one ID has more then one R STATUS ,then we need to calculate rank based on below table.</P><P></P><P><STRONG>R STATUS, RANK:</STRONG></P><P>COMP,1</P><P>PEND,2</P><P>SCOPE,3</P><P>NOTAVBLE,4</P><P></P><P></P><P>RESULTS:</P><P></P><TABLE border="1" class="jiveBorder" style="border: 1px solid #000000; border-image: none; width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>ID</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>ID NAME</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>LAYER</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>PERIOD</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>R STATUS</STRONG></TH></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">A</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">COMP</TD></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">DB</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">SCOPE</TD></TR><TR><TD style="padding: 2px;">1216</TD><TD style="padding: 2px;">D</TD><TD style="padding: 2px;">OS</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">NOTAVBLE</TD></TR><TR><TD style="padding: 2px;">1217</TD><TD style="padding: 2px;">C</TD><TD style="padding: 2px;">APP</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">PEND</TD></TR><TR><TD style="padding: 2px;">1217</TD><TD style="padding: 2px;">D</TD><TD style="padding: 2px;">DB</TD><TD style="padding: 2px;">01-01-2017-30-12-2017</TD><TD style="padding: 2px;">COMP</TD></TR></TBODY></TABLE><P></P><P>Thanks,</P><P>Gireesh</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Find-Rank-using-Firstsortedvalue/m-p/38270#M777839 gireesh1216 2020-11-25T16:16:04Z