topic Offset Flag in App Development https://community.qlik.com/t5/App-Development/Offset-Flag/m-p/1818137#M67035 <P>Hi all,<BR /><BR />I'm trying to create a schema based flag that represents that an offset was made over an invoice.<BR />For Example:</P><TABLE><TBODY><TR><TD>First_ID</TD><TD>Second_ID</TD><TD>Sum</TD><TD>Flag</TD></TR><TR><TD>330028574</TD><TD>210509309</TD><TD>-142</TD><TD>1</TD></TR><TR><TD>330028575</TD><TD>210509411</TD><TD>456</TD><TD>1</TD></TR><TR><TD>330028580</TD><TD>210509727</TD><TD>-141</TD><TD>1</TD></TR><TR><TD>330028576</TD><TD>210509214</TD><TD>350</TD><TD>1</TD></TR><TR><TD>330028577</TD><TD>210509679</TD><TD>324</TD><TD>1</TD></TR><TR><TD>330028580</TD><TD>210509727</TD><TD>282</TD><TD>1</TD></TR><TR><TD>330028614</TD><TD>210516854</TD><TD>-420.93</TD><TD>0</TD></TR><TR><TD>330028614</TD><TD>210516854</TD><TD>420.93</TD><TD>0</TD></TR><TR><TD>330028577</TD><TD>210509679</TD><TD>-162</TD><TD>1</TD></TR><TR><TD>330028576</TD><TD>210509214</TD><TD>-175</TD><TD>1</TD></TR><TR><TD><STRONG>330028589</STRONG></TD><TD><STRONG>210525919</STRONG></TD><TD><STRONG>1303.13</STRONG></TD><TD>1</TD></TR><TR><TD>330028606</TD><TD>210516376</TD><TD>1197</TD><TD>1</TD></TR><TR><TD>330028606</TD><TD>210516376</TD><TD>-399</TD><TD>1</TD></TR><TR><TD>330028617</TD><TD>210516626</TD><TD>280</TD><TD>1</TD></TR><TR><TD>330028575</TD><TD>210509411</TD><TD>-228</TD><TD>1</TD></TR><TR><TD><STRONG>330028589</STRONG></TD><TD><STRONG>210525919</STRONG></TD><TD><STRONG>-3127.51</STRONG></TD><TD>0</TD></TR><TR><TD><STRONG>330028589</STRONG></TD><TD><STRONG>210525919</STRONG></TD><TD><STRONG>3127.51</STRONG></TD><TD>0</TD></TR></TBODY></TABLE><P><BR />Since&nbsp;330028589 &amp;&nbsp;&nbsp;210525919 showed twice, once without minus sign and once with minus sign for the same amount,<BR />I can tell that an offset was made and therefore I would like to put a flag of zero near them for example.<BR /><BR />I've tried to use some peek and fabs function to get that:</P><P>T1:<BR />LOAD * Inline<BR />[<BR />First_ID, Second_ID, Sum<BR />330028574, 210509309, -142.00<BR />330028575, 210509411, 456.00<BR />330028589, 210525919, -3127.51<BR />330028580, 210509727, -141.00<BR />330028576, 210509214, 350.00<BR />330028577, 210509679, 324.00<BR />330028580, 210509727, 282.00<BR />330028614, 210516854, -420.93<BR />330028589, 210525919, 3127.51<BR />330028577, 210509679, -162.00<BR />330028576, 210509214, -175.00<BR />330028589, 210525919, 1303.13<BR />330028606, 210516376, 1197.00<BR />330028606, 210516376, -399.00<BR />330028614, 210516854, 420.93<BR />330028617, 210516626, 280.00<BR />330028575, 210509411, -228.00<BR />];</P><P>NoConcatenate</P><P>T2:<BR />LOAD *,<BR />fAbs(Sum) as fabs<BR />Resident T1;</P><P>T3:</P><P>LOAD</P><P>*,</P><P>IF(First_ID=Peek('First_ID') AND Second_ID=Peek('Second_ID') AND fabs=peek('fabs'), 0, 1) AS Flag_Is_Not_Offsetting</P><P>RESIDENT T2</P><P>ORDER BY First_ID, Second_ID, fabs ASC;<BR />DROP Tables T1,T2;<BR /><BR />But it's not good enough, the second value doesn't get the flag and I don't know how to deal with ordering the keys in such a way that a random value with&nbsp; the concurrent keys won't disturb.<BR /><BR /><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Untitled.jpg" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/57625i9C350FC574A6A79E/image-size/large?v=v2&amp;px=999" role="button" title="Untitled.jpg" alt="Untitled.jpg" /></span><BR /><BR /></P><P>Thanks.<BR /><BR /><BR /></P> Sun, 27 Jun 2021 12:40:01 GMT shayd 2021-06-27T12:40:01Z Offset Flag https://community.qlik.com/t5/App-Development/Offset-Flag/m-p/1818137#M67035 <P>Hi all,<BR /><BR />I'm trying to create a schema based flag that represents that an offset was made over an invoice.<BR />For Example:</P><TABLE><TBODY><TR><TD>First_ID</TD><TD>Second_ID</TD><TD>Sum</TD><TD>Flag</TD></TR><TR><TD>330028574</TD><TD>210509309</TD><TD>-142</TD><TD>1</TD></TR><TR><TD>330028575</TD><TD>210509411</TD><TD>456</TD><TD>1</TD></TR><TR><TD>330028580</TD><TD>210509727</TD><TD>-141</TD><TD>1</TD></TR><TR><TD>330028576</TD><TD>210509214</TD><TD>350</TD><TD>1</TD></TR><TR><TD>330028577</TD><TD>210509679</TD><TD>324</TD><TD>1</TD></TR><TR><TD>330028580</TD><TD>210509727</TD><TD>282</TD><TD>1</TD></TR><TR><TD>330028614</TD><TD>210516854</TD><TD>-420.93</TD><TD>0</TD></TR><TR><TD>330028614</TD><TD>210516854</TD><TD>420.93</TD><TD>0</TD></TR><TR><TD>330028577</TD><TD>210509679</TD><TD>-162</TD><TD>1</TD></TR><TR><TD>330028576</TD><TD>210509214</TD><TD>-175</TD><TD>1</TD></TR><TR><TD><STRONG>330028589</STRONG></TD><TD><STRONG>210525919</STRONG></TD><TD><STRONG>1303.13</STRONG></TD><TD>1</TD></TR><TR><TD>330028606</TD><TD>210516376</TD><TD>1197</TD><TD>1</TD></TR><TR><TD>330028606</TD><TD>210516376</TD><TD>-399</TD><TD>1</TD></TR><TR><TD>330028617</TD><TD>210516626</TD><TD>280</TD><TD>1</TD></TR><TR><TD>330028575</TD><TD>210509411</TD><TD>-228</TD><TD>1</TD></TR><TR><TD><STRONG>330028589</STRONG></TD><TD><STRONG>210525919</STRONG></TD><TD><STRONG>-3127.51</STRONG></TD><TD>0</TD></TR><TR><TD><STRONG>330028589</STRONG></TD><TD><STRONG>210525919</STRONG></TD><TD><STRONG>3127.51</STRONG></TD><TD>0</TD></TR></TBODY></TABLE><P><BR />Since&nbsp;330028589 &amp;&nbsp;&nbsp;210525919 showed twice, once without minus sign and once with minus sign for the same amount,<BR />I can tell that an offset was made and therefore I would like to put a flag of zero near them for example.<BR /><BR />I've tried to use some peek and fabs function to get that:</P><P>T1:<BR />LOAD * Inline<BR />[<BR />First_ID, Second_ID, Sum<BR />330028574, 210509309, -142.00<BR />330028575, 210509411, 456.00<BR />330028589, 210525919, -3127.51<BR />330028580, 210509727, -141.00<BR />330028576, 210509214, 350.00<BR />330028577, 210509679, 324.00<BR />330028580, 210509727, 282.00<BR />330028614, 210516854, -420.93<BR />330028589, 210525919, 3127.51<BR />330028577, 210509679, -162.00<BR />330028576, 210509214, -175.00<BR />330028589, 210525919, 1303.13<BR />330028606, 210516376, 1197.00<BR />330028606, 210516376, -399.00<BR />330028614, 210516854, 420.93<BR />330028617, 210516626, 280.00<BR />330028575, 210509411, -228.00<BR />];</P><P>NoConcatenate</P><P>T2:<BR />LOAD *,<BR />fAbs(Sum) as fabs<BR />Resident T1;</P><P>T3:</P><P>LOAD</P><P>*,</P><P>IF(First_ID=Peek('First_ID') AND Second_ID=Peek('Second_ID') AND fabs=peek('fabs'), 0, 1) AS Flag_Is_Not_Offsetting</P><P>RESIDENT T2</P><P>ORDER BY First_ID, Second_ID, fabs ASC;<BR />DROP Tables T1,T2;<BR /><BR />But it's not good enough, the second value doesn't get the flag and I don't know how to deal with ordering the keys in such a way that a random value with&nbsp; the concurrent keys won't disturb.<BR /><BR /><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Untitled.jpg" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/57625i9C350FC574A6A79E/image-size/large?v=v2&amp;px=999" role="button" title="Untitled.jpg" alt="Untitled.jpg" /></span><BR /><BR /></P><P>Thanks.<BR /><BR /><BR /></P> Sun, 27 Jun 2021 12:40:01 GMT https://community.qlik.com/t5/App-Development/Offset-Flag/m-p/1818137#M67035 shayd 2021-06-27T12:40:01Z Re: Offset Flag https://community.qlik.com/t5/App-Development/Offset-Flag/m-p/1818161#M67039 <P>Hi,</P><P>Maybe just left join T1 back to itself negating the Sum? So after your inline load;</P><LI-CODE lang="javascript">Left Join (T1) LOAD First_ID, Second_ID, -Sum AS Sum, 'Y' as matched Resident T1; final: NoConcatenate Load First_ID, Second_ID, Sum, If(matched='Y',0,1) AS Flag Resident T1; DROP Table T1;</LI-CODE><P>Cheers,</P><P>Chris.</P> Sun, 27 Jun 2021 18:28:20 GMT https://community.qlik.com/t5/App-Development/Offset-Flag/m-p/1818161#M67039 chrismarlow 2021-06-27T18:28:20Z Re: Offset Flag https://community.qlik.com/t5/App-Development/Offset-Flag/m-p/1818258#M67051 <P>Excellent idea, Thank you&nbsp;<SPAN>chrismarlow, that worked!&nbsp;<span class="lia-unicode-emoji" title=":thumbs_up:">👍</span></SPAN></P> Mon, 28 Jun 2021 09:53:20 GMT https://community.qlik.com/t5/App-Development/Offset-Flag/m-p/1818258#M67051 shayd 2021-06-28T09:53:20Z