3 Replies Latest reply: Mar 16, 2012 10:28 AM by Stefan Wühl RSS

    Count distinct is ignoring restrictions.

      Good afternoon, ive an issue when i'm using the Count(distinct(... function.

       

      It seems to make Qlikview completely ignore any restrictions in the code!

       

      For example :-

       

      For what ever reason what ever gets loaded in the 1st table the same total gets loaded in Copy bill totals irrespective of what the true value is!

       

      Its like :-

       

      Bill sent totals | Copy Bill sent totals

               5           |          5

               3           |          3

               7           |          7

               9           |          9

       

       

      Is there a reason why this is happening?

       

      Paul.

       

      BILL_SENT_TOTALS

      LOAD CA,

      count(distinct(Number_Of_Billing_Document)) as Bills

      Resident FKKVKP

      where Contact_Text='Bill Sent'

      Group by CA

      ;

      COPY_BILLSENT_TOTALS:

      LOAD CA,

      count(distinct(Number_Of_Billing_Document)) as Copy_Bills

      Resident FKKVKP

      where Contact_Text='Copy Bill'

      Group by CA

        • Count distinct is ignoring restrictions.
          Stefan Wühl

          Hard to say without knowing anything about your table FKKVKP. I don't think that's an issue in principal, for example  something like this does produce different results for Bills and Copy_Bills:

           

          FKKVKP:

          LOAD

          if(RAND()<0.5,'Bill Sent','Copy Bill') as Contact_Text,

          ceil(RAND()*10) as CA,

          floor(RAND()*10) as [Number_Of_Billing_Document]

          AutoGenerate 300;

            

          BILL_SENT_TOTALS:

          LOAD CA,

          count(distinct(Number_Of_Billing_Document)) as Bills

          Resident FKKVKP

          where Contact_Text='Bill Sent'

          Group by CA

          ;

          COPY_BILLSENT_TOTALS:

          LOAD CA,

          count(distinct(Number_Of_Billing_Document)) as Copy_Bills

          Resident FKKVKP

          where Contact_Text='Copy Bill'

          Group by CA

          ;

            • Count distinct is ignoring restrictions.

              My Full Code is below. The issue im having is when im using the Count(Distinct(Number_of_Billing_Document)) as... I then apply restriction such as Where Contact_Text='Copy Bill'.

               

              if the 1st row called 'Bill sent' is 5 all the following rows will come out with 5 as a numerical amount the only exception to this is for example if a customer hasnt had a 'Copy Email' or 'Bill Sent' criteria fulfilled.

               

              But for example if 1 customer has had 4 'Bill Sent' and 2 'Email bill' Qlikview output will say 4 'Bill sent' and 4 'Email bill'.

               

              I just cant see why this is happening.

               

               

               

              Table_1:

              LOAD BP,

              if(wildmatch(Account_Det_ID,'C*'), 'Commercial', 'Domestic') AS Domestic_Commercial,

              CA

              FROM

              *

              ;

              Table_2:

              LOAD BP_Type

              FROM

              *

              where BP_Type='C001'

              ;

              Table_3:

              Left Join(TABLE_1)

              LOAD BP_Type,

              BP

              FROM

              *

              where exists(BP_Type)

              ;

              Table_4:

              Left Join(TABLE_1)

              LOAD Contact_Action,

              Date,

              BP

              FROM

              *

              where exists(BP)

              and Date>='2011-04-01'

              and Date<='2012-03-31'

              ;

              Table_5:

              Left Join(TABLE_1)

              LOAD Contact_Action,

              Contact_Class,

              Contact_Text

              FROM

              *

              where exists(Contact_Action)

              ;

              Table_6:

              Left Join(TABLE_1)

              LOAD Installation,

              Move_Out_Date,

              Contract,

              CA

              FROM

              *

              where exists(CA)

              and Move_Out_Date='31/12/9999'

              ;

              Table_7:

              Left Join(TABLE_1)

              LOAD Start_Of_Billing_Period,

              Number_Of_Billing_Document,

              Created_On,

              End_Of_Billing_Period,

              Contract

              FROM

              *

              where exists(Contract)

              and Start_Of_Billing_Period >= '2011-04-01'

              and End_Of_Billing_Period <= '2012-03-31'

              and Created_On >= '2011-04-01'

              and Created_On <= '2012-03-31'

              ;

              BILLSENT_TOTALS:

              LOAD CA,

              count(distinct(Number_Of_Billing_Document)) as Bills

              Resident TABLE_1

              where Contact_Text='Bill Sent'

              Group by CA

              ;

              COPY_BILLSENT_TOTALS:

              LOAD CA,

              count(distinct(Number_Of_Billing_Document)) as Copy_Bills

              Resident TABLE_1

              where Contact_Text='Copy Bill'

              Group by CA

              ;

              EMAIL_COPY_BILL:

              LOAD CA,

              count(distinct(Number_Of_Billing_Document)) as Email_Bills

              Resident TABLE_1

              where Contact_Text='Email Bill'

              Group by CA

              ;

              Table_8:

              Left Join(TABLE_1)

              LOAD Date_Of_Issue,

              Dunning_Procedure,

              Dunning_Level,      

              CA

              FROM

              *

              where exists(CA)

              and Date_Of_Issue >= '2011-04-01'

              and Date_Of_Issue <= '2012-03-31'

              ;

              GEN_SPEC_TOTALS:

              LOAD CA,

              count(distinct(Number_Of_Billing_Document)) as Gen_spc_tot

              Resident TABLE_1

              where Dunning_Procedure='CD'

              and Dunning_Level='1'

              or Dunning_Level='2'

              Group by CA

              ;

               

                • Count distinct is ignoring restrictions.
                  Stefan Wühl

                  It's quite hard for my little mind to follow your load sequence, could you maybe upload a set of input sample files (xls, csv, or just INLINE tables) that help demonstrating what you see?

                   

                  As a test, you could try removing the where clause and putting the condition into the aggregation, like

                   

                  TEST:

                  LOAD CA,

                  count(distinct(if( Contact_Text='Copy Bill',Number_Of_Billing_Document))) as Copy_BillsTest,

                  count(distinct(if( Contact_Text='Bill Sent',Number_Of_Billing_Document))) as BillsTest,

                  count(distinct(Number_Of_Billing_Document)) as TotalBillsTest

                  Resident FKKVKP

                  Group by CA

                  ;