topic Specify field when the field-name is the same across multiple tables in App Development

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

jakobjensen — Fri, 25 Sep 2020 07:54:12 GMT

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

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

stevedark — Fri, 25 Sep 2020 08:35:34 GMT

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

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

Taoufiq_Zarra — Fri, 25 Sep 2020 08:39:56 GMT

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