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

[jakobjensen]

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"?

[stevedark]

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 ?