Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
;
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
;
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
;