Count of Records with specific criteria in another table

[rougeherring]

Hi,

I'm trying to figure out how to create a field within a load statement with the number of specific type of records in another table. In the example below, I want the number of employees for each location (20k = count of records in Employees that = location and makes between 20k and 29.9k)

Locations:

Location, 20k_Employees,30k_Employees,50k_Employees

New York,0,1,0

Miami, 0,1,1

Dallas 1,0,0

Employees:

Employee_Name,Salary,Location

Brian, 34000, Miami

Steve, 27000, Dallas

Anna, 30000, New York

Paula, 50000, Miami

[sinanozdemir]

Hi Brandon,

How about joining these two tables:

At the end you will have the fields from Locations table, here is the final data model:

Hope this helps.

[sunny_talwar]

Isn't the Location table in itself telling you how many employees at a location?

New York have 1 employee in 30k

Miami have 1 in 30k and another in 50k

and so on...

Now sure if you are looking for something else? or if the location table is what you are trying to build?

[petter]

Employees:

LOAD * INLINE [

Employee_Name,Salary,Location

Brian, 34000, Miami

Steve, 27000, Dallas

Anna, 30000, New York

Paula, 50000, Miami

];

Locations:

LOAD

  Location,

  Sum ( E20 ) AS 20K_Employees,

  Sum ( E30 ) AS 30K_Employees,

  Sum ( E50 ) AS 50K_Employees

GROUP BY

  Location;

LOAD

  Location,

  -(Salary >= 20000 Salary < 30000) AS E20 ,

  -(Salary >= 30000 AND Salary < 50000) AS E30 ,

  -(Salary >= 50000) AS E50

RESIDENT

  Employees;

[rougeherring]

Thank you, The location table is what I'm trying to build.

[sunny_talwar]

Responses by Sinan and Petter seems to be doing the job. Have you looked at those solutions?

[rougeherring]

Sinan's recommendation includes the values but I need to add the columns [20k_Employees], [30k_Employees] ,[50k_Employees] to the Locations table based on data in the Employees table.

I also looked at Petter's recommendation but it will not run in my load editor.

I'm looking for code to find the count of matching rows from Employees based on criteria.

[maxgro]

Employees:

LOAD * INLINE [

Employee_Name,Salary,Location

Brian, 34000, Miami

Steve, 27000, Dallas

Anna, 30000, New York

Paula, 50000, Miami

Anna1, 30000, New York

Anna2, 20000, New York

];

Locations:

LOAD

  Location,

  sum(-(Salary >= 20000 AND Salary < 30000)) as [20K_Employees] ,

  sum(-(Salary >= 30000 AND Salary < 50000)) as [30K_Employees] ,

  sum(-(Salary >= 50000)) as [50K_Employees]

RESIDENT

  Employees

GROUP BY

  Location;

[sunny_talwar]

May be this:

Table:

LOAD *,

  If(Salary >= 20000 and Salary < 30000, 1, 0) as [20k_Employees],

  If(Salary >= 30000 and Salary < 50000, 1, 0) as [30k_Employees],

  If(Salary >= 50000, 1, 0) as [50k_Employees];

LOAD * Inline [

Employee_Name, Salary, Location

Brian, 34000, Miami

Steve, 27000, Dallas

Anna, 30000, New York

Paula, 50000, Miami

];