topic Re: FirstSortedValue with two criteria in QlikView https://community.qlik.com/t5/QlikView/FirstSortedValue-with-two-criteria/m-p/1735222#M591926 <P>Finally, I resolved my issue. Below the script:</P><P>NoConcatenate<BR />[RESULT_TEMP]:<BR />Load Distinct<BR />InvoiceNumber,<BR />FirstSortedValue(DISTINCT NetAmountConverted, -EntryDate and -EntryTime) + SUM(ChargesFees) AS TempVlaue<BR />Resident INV_TEMP<BR />Group By InvoiceNumber;</P> Wed, 12 Aug 2020 12:15:19 GMT Black_Hole 2020-08-12T12:15:19Z FirstSortedValue with two criteria https://community.qlik.com/t5/QlikView/FirstSortedValue-with-two-criteria/m-p/1735201#M591925 <P>Hello all,&nbsp;</P><P>I would like to use the function FirstSortedValued by considering two criteria inside.&nbsp;</P><P>I try to affect the "ChargesFees" value to the "NetAmountConverted" for only the row which has the oldest date and time.</P><P>For example:</P><P><STRONG>RowNo | InvoiceNumber&nbsp; |&nbsp; NetAmountConverted&nbsp; |&nbsp; ChargesFees&nbsp; |&nbsp; EntryDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; EntryTime</STRONG></P><P>1<STRONG> | </STRONG>INV01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <STRONG>|&nbsp;</STRONG>&nbsp; &nbsp;1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<STRONG>|&nbsp; &nbsp;</STRONG>60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <STRONG>|&nbsp; </STRONG>20200123 <STRONG>|</STRONG>&nbsp; 1501</P><P>2<STRONG> |</STRONG>INV01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <STRONG>|&nbsp;</STRONG>&nbsp; &nbsp;2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<STRONG>|&nbsp; &nbsp;</STRONG>60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <STRONG>|&nbsp; </STRONG>20200123 <STRONG>|</STRONG>&nbsp; 1502</P><P>3<STRONG> |</STRONG>INV02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <STRONG>|&nbsp;</STRONG>&nbsp; &nbsp;5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<STRONG>|&nbsp; &nbsp;</STRONG>12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<STRONG>|&nbsp; </STRONG>20191208&nbsp;<STRONG>|</STRONG>&nbsp; 1620</P><P>4<STRONG> |</STRONG>INV02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <STRONG>|&nbsp;</STRONG>&nbsp; &nbsp;100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<STRONG>|&nbsp; &nbsp;</STRONG>12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<STRONG>|&nbsp; </STRONG>20200706&nbsp;<STRONG>|</STRONG>&nbsp; 0935</P><P>&nbsp;</P><P>&gt; The result expected:</P><P><STRONG>RowNo | InvoiceNumber&nbsp; |&nbsp; TempValue&nbsp; &nbsp;&nbsp;</STRONG></P><P>1<STRONG> |</STRONG>INV01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <STRONG>|&nbsp;</STRONG>&nbsp; &nbsp;1060&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;<STRONG>&nbsp;</STRONG></P><P>2<STRONG> |</STRONG>INV01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <STRONG>|&nbsp;</STRONG>&nbsp; &nbsp;2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;<STRONG>&nbsp;&nbsp;</STRONG></P><P>3<STRONG> |</STRONG>INV02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <STRONG>|&nbsp;</STRONG>&nbsp; &nbsp;5012&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<STRONG>&nbsp;&nbsp;</STRONG></P><P>4<STRONG> |</STRONG>INV02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <STRONG>|&nbsp;</STRONG>&nbsp; &nbsp;100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;<STRONG>&nbsp;&nbsp;</STRONG></P><P>&nbsp;</P><P>&gt; My current script:</P><P><EM>NoConcatenate</EM><BR />[RESULT_TEMP]:<BR /><STRONG>Load Distinct</STRONG><BR />InvoiceNumber,<BR />FirstSortedValue(SUM(NetAmountConverted)+SUM(ChargesFees), -EntryDate and -EntryTime) AS TempVlaue<BR /><STRONG>Resident</STRONG> INV_TEMP<BR /><STRONG>Group By</STRONG> InvoiceNumber;</P><P><STRONG>Please could you highlight me what is not correct in my current script.</STRONG></P><P>Thank you in advance for your help.</P><P>&nbsp;</P> Sat, 16 Nov 2024 00:09:29 GMT https://community.qlik.com/t5/QlikView/FirstSortedValue-with-two-criteria/m-p/1735201#M591925 Black_Hole 2024-11-16T00:09:29Z Re: FirstSortedValue with two criteria https://community.qlik.com/t5/QlikView/FirstSortedValue-with-two-criteria/m-p/1735222#M591926 <P>Finally, I resolved my issue. Below the script:</P><P>NoConcatenate<BR />[RESULT_TEMP]:<BR />Load Distinct<BR />InvoiceNumber,<BR />FirstSortedValue(DISTINCT NetAmountConverted, -EntryDate and -EntryTime) + SUM(ChargesFees) AS TempVlaue<BR />Resident INV_TEMP<BR />Group By InvoiceNumber;</P> Wed, 12 Aug 2020 12:15:19 GMT https://community.qlik.com/t5/QlikView/FirstSortedValue-with-two-criteria/m-p/1735222#M591926 Black_Hole 2020-08-12T12:15:19Z