Can I qualify a table in count expression?

[#1]

Hi Folks

I have two tables, one is "master" table and the other is "detail" table. I need to count the rows in the detail table considering the selected value in the master table. For example: I need to count the number of complains of a customer, the master table is the customer table, and the complains table is the detail table, both tables have an CUSTOMER_ID field.

I need to count records from detail table like this: count(DISTINCT CUSTOMER_ID). But I got incorrect results.

Thanks in advance.

[sunny_talwar]

Add a new column in your master table:

LOAD CUSTOMER_ID,

          AutoNumber(CUSTOMER_ID) as xyz

FROM Source;

Now try doing a distinct count of xyz -> Count(DISTINCT xyz)

or you can look at the max of xyz -> Max(xyz)

[#3]

Thanks sunindia‌

I would like to explain my question. I have two measures:

Total_Q_Customer= count(DISTINCT CUSTOMER_ID) from "Master" table

and

Total_Q_Complains= count(DISTINCT CUSTOMER_ID) from "Detail" table

In order to have two diferent KPI in the same sheet. Both tables have CUSTOMER_ID in order to link.

I would like to know, how to specify to QLIK that the count aggregate funcion of Total_Q_Complains should be from the "Detail" table and not from de "Master" table?

[sunny_talwar · Accepted solution]

The method proposed earlier would have worked, but if you don't want to make changes to the script. Then pick one column from each table which doesn't exists in the other table. Lets call these two columns as Column_Master and Column_Detail. and use this:

Count of CustomerID in Master:

Count(DISTINCT {<Column_Master = {"=Len(Trim(Column_Master)) > 0"}>}CustomerID)

Count of CustomerID in Detail:

Count(DISTINCT {<Column_Detail= {"=Len(Trim(Column_Detail)) > 0"}>}CustomerID)

[#5]

It Works!!!! sunindia‌

Would please explain me the sintaxis?

Very Thankful!!!!

[sunny_talwar]

So the set analysis is doing a count when the field is not blank (null). So since Column_Master won't exist in Detail table it will only count CustomerID from the Master table. Does that make sense?