Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
jakobjensen
Contributor II
Contributor II

Specify field when the field-name is the same across multiple tables

I have two tables "Customer" and "Employees" where we have a field "ID" in both of the tables.

Say I want to do a "COUNT(DISTINCT(ID))" for both, how do I specify when the "ID" should be from "Customer"-table and when it should be from "Employess"?

2 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

You will want to create extra fields in each of the tables at load. If you need a distinct count you will need to put that in both tables. If you only need a count of rows adding a 1 to each row in each table will be much more efficient. I always add a counter to every table I create, to have an efficient way of counting rows.

The code would be like this:

 

Customer:
LOAD
  1 as CustomerCount,
  ID,
  ID as CustomerID,
  ...

Employee:
LOAD
  1 as EmployeeCount,
  ID,
  ID as EmployeeID,
  ...

 

 

You can then use the following expressions;

sum(CustomerCount)

sum(EmployeeCount)

count(DISTINCT CustomerID)

count(DISTINCT EmployeeID)

or for the superset of IDs the expression you already have

count(DISTINCT ID)

Hope that helps.

Steve

Taoufiq_Zarra

@jakobjensen  can you share a sample data and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉