Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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

3 Replies
MVP
MVP

Count distinct is ignoring restrictions.

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

;

Not applicable

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

;

MVP
MVP

Count distinct is ignoring restrictions.

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

;