topic Re: Count of Records with And Not Condition. in Management & Governance https://community.qlik.com/t5/Management-Governance/Count-of-Records-with-And-Not-Condition/m-p/2439800#M26992 <P>Perhaps:</P> <P>Temp:<BR />LOAD * From &lt;Sample Dataset&gt;;</P> <P>Join<BR />Load P_BK,<BR />Count(P_BK)-1 as Duplicates#<BR />Resident Temp<BR />Group By P_BK;</P> <P>Final:<BR />LOAD *,<BR />If(Duplicates# = 0 and CCCode = 'SC' and RecTypeName = 'RP', 'ORP Only',<BR />If(Duplicates# = 0 and CCCode = 'SC' and RecTypeName = 'DB', 'SC Only',<BR />If(Duplicates# &gt; 0, 'SC and ORP'))) as [Expected O/P]<BR />Resident Temp;<BR />DROP Table Temp;<BR />DROP Field Duplicates#;</P> Wed, 10 Apr 2024 13:42:20 GMT BrunPierre 2024-04-10T13:42:20Z Count of Records with And Not Condition. https://community.qlik.com/t5/Management-Governance/Count-of-Records-with-And-Not-Condition/m-p/2439742#M26989 <P>Hi Team,&nbsp;</P> <P>I have a requirement , where i need to find the count(Distinct P_BK)</P> <P>Note : P_BK should be checked in all the rows and find the distinct count</P> <P>Sample Dataset:</P> <TABLE> <TBODY> <TR> <TD width="73">Date</TD> <TD width="64">P_BK</TD> <TD width="64">CCCode</TD> <TD width="97">RecTypeName</TD> </TR> <TR> <TD>2024-03-01</TD> <TD>4194688</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-03-14</TD> <TD>4195660</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-03-15</TD> <TD>4196011</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-02-29</TD> <TD>4196105</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-03-26</TD> <TD>4196518</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-03-13</TD> <TD>4196628</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-04-05</TD> <TD>4197716</TD> <TD>SC</TD> <TD>RP</TD> </TR> <TR> <TD>2024-03-15</TD> <TD>4198394</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-03-18</TD> <TD>4198650</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-03-26</TD> <TD>4199911</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-03-08</TD> <TD>4201398</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-04-01</TD> <TD>4204063</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-04-01</TD> <TD>4204063</TD> <TD>SC</TD> <TD>RP</TD> </TR> <TR> <TD>2024-04-04</TD> <TD>4248356</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-04-04</TD> <TD>4248356</TD> <TD>SC</TD> <TD>RP</TD> </TR> <TR> <TD>2024-03-28</TD> <TD>4314267</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-04-05</TD> <TD>4318384</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-04-04</TD> <TD>4324030</TD> <TD>SC</TD> <TD>DB</TD> </TR> <TR> <TD>2024-04-01</TD> <TD>4333052</TD> <TD>SC</TD> <TD>DB</TD> </TR> </TBODY> </TABLE> <P>Criteria 1 - (CCCode = SC and RecTypeName = DB ) and NOT&nbsp;(CCCode = SC and RecTypeName = RP )&nbsp; then Expected O/P&nbsp; = SC Only</P> <P>Criteria 2 - (CCCode = SC and RecTypeName = RP ) and NOT&nbsp;(CCCode = SC and RecTypeName = DB)&nbsp; then Expected O/P&nbsp; = ORP Only</P> <P>Criteria 3 - (CCCode = SC and RecTypeName = RP ) and&nbsp; (CCCode = SC and RecTypeName = DB)&nbsp; then Expected O/P&nbsp; = SC and ORP</P> <P>&nbsp;</P> <P>Expected_Output</P> <TABLE width="386"> <TBODY> <TR> <TD width="73">Date</TD> <TD width="64">P_BK</TD> <TD width="64">CCCode</TD> <TD width="97">RecTypeName</TD> <TD width="88">Expected 0/P</TD> </TR> <TR> <TD>2024-03-01</TD> <TD>4194688</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC Only</TD> </TR> <TR> <TD>2024-03-14</TD> <TD>4195660</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC Only</TD> </TR> <TR> <TD>2024-03-15</TD> <TD>4196011</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC Only</TD> </TR> <TR> <TD>2024-02-29</TD> <TD>4196105</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC Only</TD> </TR> <TR> <TD>2024-03-26</TD> <TD>4196518</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC Only</TD> </TR> <TR> <TD>2024-03-13</TD> <TD>4196628</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC Only</TD> </TR> <TR> <TD>2024-04-05</TD> <TD>4197716</TD> <TD>SC</TD> <TD>RP</TD> <TD>ORP Only</TD> </TR> <TR> <TD>2024-03-15</TD> <TD>4198394</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC Only</TD> </TR> <TR> <TD>2024-03-18</TD> <TD>4198650</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC Only</TD> </TR> <TR> <TD>2024-03-26</TD> <TD>4199911</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC Only</TD> </TR> <TR> <TD>2024-03-08</TD> <TD>4201398</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC Only</TD> </TR> <TR> <TD>2024-04-01</TD> <TD>4204063</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC and ORP</TD> </TR> <TR> <TD>2024-04-01</TD> <TD>4204063</TD> <TD>SC</TD> <TD>RP</TD> <TD>SC and ORP</TD> </TR> <TR> <TD>2024-04-04</TD> <TD>4248356</TD> <TD>SC</TD> <TD>DB</TD> <TD>SC and ORP</TD> </TR> <TR> <TD>2024-04-04</TD> <TD>4248356</TD> <TD>SC</TD> <TD>RP</TD> <TD>SC and ORP</TD> </TR> <TR> <TD>2024-03-28</TD> <TD>4314267</TD> <TD>SC</TD> <TD>DB</TD> <TD>ORP Only</TD> </TR> <TR> <TD>2024-04-05</TD> <TD>4318384</TD> <TD>SC</TD> <TD>DB</TD> <TD>ORP Only</TD> </TR> <TR> <TD>2024-04-04</TD> <TD>4324030</TD> <TD>SC</TD> <TD>DB</TD> <TD>ORP Only</TD> </TR> <TR> <TD>2024-04-01</TD> <TD>4333052</TD> <TD>SC</TD> <TD>DB</TD> <TD>ORP Only</TD> </TR> </TBODY> </TABLE> Wed, 10 Apr 2024 10:47:23 GMT https://community.qlik.com/t5/Management-Governance/Count-of-Records-with-And-Not-Condition/m-p/2439742#M26989 devips 2024-04-10T10:47:23Z Re: Count of Records with And Not Condition. https://community.qlik.com/t5/Management-Governance/Count-of-Records-with-And-Not-Condition/m-p/2439800#M26992 <P>Perhaps:</P> <P>Temp:<BR />LOAD * From &lt;Sample Dataset&gt;;</P> <P>Join<BR />Load P_BK,<BR />Count(P_BK)-1 as Duplicates#<BR />Resident Temp<BR />Group By P_BK;</P> <P>Final:<BR />LOAD *,<BR />If(Duplicates# = 0 and CCCode = 'SC' and RecTypeName = 'RP', 'ORP Only',<BR />If(Duplicates# = 0 and CCCode = 'SC' and RecTypeName = 'DB', 'SC Only',<BR />If(Duplicates# &gt; 0, 'SC and ORP'))) as [Expected O/P]<BR />Resident Temp;<BR />DROP Table Temp;<BR />DROP Field Duplicates#;</P> Wed, 10 Apr 2024 13:42:20 GMT https://community.qlik.com/t5/Management-Governance/Count-of-Records-with-And-Not-Condition/m-p/2439800#M26992 BrunPierre 2024-04-10T13:42:20Z