Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Can I qualify a table in count expression?

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Can I qualify a table in count expression?

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 Replies
MVP
MVP

Re: Can I qualify a table in count expression?

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)

Not applicable

Re: Can I qualify a table in count expression?

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.

Captura.PNG

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?

MVP
MVP

Re: Can I qualify a table in count expression?

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)

Not applicable

Re: Can I qualify a table in count expression?

It Works!!!! sunindia

Would please explain me the sintaxis?

Very Thankful!!!!

MVP
MVP

Re: Can I qualify a table in count expression?

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?