topic Help defining condition in to a script in Connectivity & Data Prep https://community.qlik.com/t5/Connectivity-Data-Prep/Help-defining-condition-in-to-a-script/m-p/1698831#M5118 <P>Hello,</P> <P>I have trouble defining a condition in to my script. I have Loaded a table from a single source and I need to filter it based on the below condition.</P> <P>Within the same serial number(that is serial + suffix set). Look for the First Lost Charges entry, then look within the suffix where that charge occurred and if there is a Lost Credits entry - ignore/continue to the next Lost charges, if there is no Lost Credits entry for that suffix, but there is a Lost Charges - load all the follow up data for that serial including the Lost Charges (not just that suffix).</P> <P>Example Data:</P> <TABLE width="383"> <TBODY> <TR> <TD>Serial</TD> <TD>Sfx</TD> <TD>Ser|Sfx</TD> <TD>Value</TD> <TD>Charge Date</TD> <TD>Charge Type</TD> </TR> <TR> <TD width="39">96</TD> <TD width="18">1</TD> <TD width="39">96|1</TD> <TD width="80">3.50</TD> <TD width="89">30/09/2002</TD> <TD width="118">Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/10/2002</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>30/11/2002</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/12/2002</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/01/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>28/02/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/03/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>30/04/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/05/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>30/06/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/07/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/08/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>112.50</TD> <TD>14/10/2003</TD> <TD>Lost Charges</TD> </TR> <TR> <TD>96</TD> <TD>2</TD> <TD>96|2</TD> <TD>3.50</TD> <TD>30/11/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>2</TD> <TD>96|2</TD> <TD>3.50</TD> <TD>31/12/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>2</TD> <TD>96|2</TD> <TD>3.50</TD> <TD>31/01/2004</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>3</TD> <TD>96|3</TD> <TD>3.50</TD> <TD>31/08/2005</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>3</TD> <TD>96|3</TD> <TD>3.50</TD> <TD>30/09/2005</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>3</TD> <TD>96|3</TD> <TD>3.50</TD> <TD>31/10/2005</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>4</TD> <TD>96|4</TD> <TD>3.50</TD> <TD>31/01/2006</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>4</TD> <TD>96|4</TD> <TD>3.50</TD> <TD>28/02/2006</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>4</TD> <TD>96|4</TD> <TD>112.50</TD> <TD>10/05/2006</TD> <TD>Lost Charges</TD> </TR> <TR> <TD>96</TD> <TD>4</TD> <TD>96|4</TD> <TD>-112.50</TD> <TD>15/05/2006</TD> <TD>Lost Credits</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>Resulting data should be:</P> <TABLE width="412px"> <TBODY> <TR> <TD width="50px">Serial</TD> <TD width="40px">Sfx</TD> <TD width="59px">Ser|Sfx</TD> <TD width="61px">Value</TD> <TD width="88px">Charge Date</TD> <TD width="114px">Charge Type</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">1</TD> <TD width="59px">96|1</TD> <TD width="61px">112.50</TD> <TD width="88px">14/10/2003</TD> <TD width="114px">Lost Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">2</TD> <TD width="59px">96|2</TD> <TD width="61px">3.50</TD> <TD width="88px">30/11/2003</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">2</TD> <TD width="59px">96|2</TD> <TD width="61px">3.50</TD> <TD width="88px">31/12/2003</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">2</TD> <TD width="59px">96|2</TD> <TD width="61px">3.50</TD> <TD width="88px">31/01/2004</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">3</TD> <TD width="59px">96|3</TD> <TD width="61px">3.50</TD> <TD width="88px">31/08/2005</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">3</TD> <TD width="59px">96|3</TD> <TD width="61px">3.50</TD> <TD width="88px">30/09/2005</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">3</TD> <TD width="59px">96|3</TD> <TD width="61px">3.50</TD> <TD width="88px">31/10/2005</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">4</TD> <TD width="59px">96|4</TD> <TD width="61px">3.50</TD> <TD width="88px">31/01/2006</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">4</TD> <TD width="59px">96|4</TD> <TD width="61px">3.50</TD> <TD width="88px">28/02/2006</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">4</TD> <TD width="59px">96|4</TD> <TD width="61px">112.50</TD> <TD width="88px">10/05/2006</TD> <TD width="114px">Lost Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">4</TD> <TD width="59px">96|4</TD> <TD width="61px">-112.50</TD> <TD width="88px">15/05/2006</TD> <TD width="114px">Lost Credits</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>Any help appreciated.</P> Thu, 23 Dec 2021 20:12:53 GMT Dparchom 2021-12-23T20:12:53Z Help defining condition in to a script https://community.qlik.com/t5/Connectivity-Data-Prep/Help-defining-condition-in-to-a-script/m-p/1698831#M5118 <P>Hello,</P> <P>I have trouble defining a condition in to my script. I have Loaded a table from a single source and I need to filter it based on the below condition.</P> <P>Within the same serial number(that is serial + suffix set). Look for the First Lost Charges entry, then look within the suffix where that charge occurred and if there is a Lost Credits entry - ignore/continue to the next Lost charges, if there is no Lost Credits entry for that suffix, but there is a Lost Charges - load all the follow up data for that serial including the Lost Charges (not just that suffix).</P> <P>Example Data:</P> <TABLE width="383"> <TBODY> <TR> <TD>Serial</TD> <TD>Sfx</TD> <TD>Ser|Sfx</TD> <TD>Value</TD> <TD>Charge Date</TD> <TD>Charge Type</TD> </TR> <TR> <TD width="39">96</TD> <TD width="18">1</TD> <TD width="39">96|1</TD> <TD width="80">3.50</TD> <TD width="89">30/09/2002</TD> <TD width="118">Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/10/2002</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>30/11/2002</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/12/2002</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/01/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>28/02/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/03/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>30/04/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/05/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>30/06/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/07/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>3.50</TD> <TD>31/08/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>1</TD> <TD>96|1</TD> <TD>112.50</TD> <TD>14/10/2003</TD> <TD>Lost Charges</TD> </TR> <TR> <TD>96</TD> <TD>2</TD> <TD>96|2</TD> <TD>3.50</TD> <TD>30/11/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>2</TD> <TD>96|2</TD> <TD>3.50</TD> <TD>31/12/2003</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>2</TD> <TD>96|2</TD> <TD>3.50</TD> <TD>31/01/2004</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>3</TD> <TD>96|3</TD> <TD>3.50</TD> <TD>31/08/2005</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>3</TD> <TD>96|3</TD> <TD>3.50</TD> <TD>30/09/2005</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>3</TD> <TD>96|3</TD> <TD>3.50</TD> <TD>31/10/2005</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>4</TD> <TD>96|4</TD> <TD>3.50</TD> <TD>31/01/2006</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>4</TD> <TD>96|4</TD> <TD>3.50</TD> <TD>28/02/2006</TD> <TD>Rental Charges</TD> </TR> <TR> <TD>96</TD> <TD>4</TD> <TD>96|4</TD> <TD>112.50</TD> <TD>10/05/2006</TD> <TD>Lost Charges</TD> </TR> <TR> <TD>96</TD> <TD>4</TD> <TD>96|4</TD> <TD>-112.50</TD> <TD>15/05/2006</TD> <TD>Lost Credits</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>Resulting data should be:</P> <TABLE width="412px"> <TBODY> <TR> <TD width="50px">Serial</TD> <TD width="40px">Sfx</TD> <TD width="59px">Ser|Sfx</TD> <TD width="61px">Value</TD> <TD width="88px">Charge Date</TD> <TD width="114px">Charge Type</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">1</TD> <TD width="59px">96|1</TD> <TD width="61px">112.50</TD> <TD width="88px">14/10/2003</TD> <TD width="114px">Lost Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">2</TD> <TD width="59px">96|2</TD> <TD width="61px">3.50</TD> <TD width="88px">30/11/2003</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">2</TD> <TD width="59px">96|2</TD> <TD width="61px">3.50</TD> <TD width="88px">31/12/2003</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">2</TD> <TD width="59px">96|2</TD> <TD width="61px">3.50</TD> <TD width="88px">31/01/2004</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">3</TD> <TD width="59px">96|3</TD> <TD width="61px">3.50</TD> <TD width="88px">31/08/2005</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">3</TD> <TD width="59px">96|3</TD> <TD width="61px">3.50</TD> <TD width="88px">30/09/2005</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">3</TD> <TD width="59px">96|3</TD> <TD width="61px">3.50</TD> <TD width="88px">31/10/2005</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">4</TD> <TD width="59px">96|4</TD> <TD width="61px">3.50</TD> <TD width="88px">31/01/2006</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">4</TD> <TD width="59px">96|4</TD> <TD width="61px">3.50</TD> <TD width="88px">28/02/2006</TD> <TD width="114px">Rental Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">4</TD> <TD width="59px">96|4</TD> <TD width="61px">112.50</TD> <TD width="88px">10/05/2006</TD> <TD width="114px">Lost Charges</TD> </TR> <TR> <TD width="50px">96</TD> <TD width="40px">4</TD> <TD width="59px">96|4</TD> <TD width="61px">-112.50</TD> <TD width="88px">15/05/2006</TD> <TD width="114px">Lost Credits</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>Any help appreciated.</P> Thu, 23 Dec 2021 20:12:53 GMT https://community.qlik.com/t5/Connectivity-Data-Prep/Help-defining-condition-in-to-a-script/m-p/1698831#M5118 Dparchom 2021-12-23T20:12:53Z