Count distinct is ignoring restrictions.

[#1]

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

[swuehl]

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

;

[#3]

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

;

[swuehl]

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

;