Count option inside of a Mapping Load with Resident table

[dawgfather]

I am attempting to reference an already-loaded (resident) table through a Mapping Load statement, and in that mapping load, conduct a COUNT of the things related to the passed field. I have Table1 loaded with information from a SQL query. Sample below: Item City A1 Dallas B1 Boston C1 Boston D1 Memphis E1 Memphis ... So in a later table load of other information (City related), I want to do a mapping (ApplyMap) and against the resident table above, pass the City name and return a distinct count of Items in that city. My logic would be to create a mapping load that would read something like this: CityItemCount:   Mapping LOAD City,         COUNT (DISTINCT Item)   Resident Table1; The ApplyMap would be something like: SQL Select     Field1,     Field2,     Field3,     CITY,         ApplyMap(‘CityItemCount’, CITY, null()) as ItemCount,     Field5,     … From MySQLDatabase; So if I passed along Boston or Memphis, I should get a "2". If I pass along Dallas, I get a "1". If I pass along Orlando (no entries) I should get a null answer. When I try this, I get an error of Field not found - CityItemCount: Mapping Load City, Count(Distinct Item) Resident Table1 Can I not do a Count inside of a Mapping Load? Or is there some other syntax that has to be used?

[dawgfather]

Putting into a readable format:

I am attempting to reference a loaded table through a Mapping Load statement, and in that mapping load, conduct a COUNT of the passed field.

I have Table1 loaded with information from a SQL query. Sample below:

Item	City
A1	Dallas
B1	Boston
C1	Boston
D1	Memphis
E1	Memphis

So in a later table load of other information (City related), I want to do a mapping (ApplyMap) and against the table above, pass the City name and return a count of Items in that city.

My logic would be to create a mapping load that would read something like this:

CityItemCount:

Mapping LOAD

             City,

COUNT (DISTINCT Item)

           Resident Table1;

The ApplyMap would be something like:

SQL Select

Field1,

Field2,

Field3,

CITY,

ApplyMap(‘CityItemCount’, CITY, null()) as ItemCount,

Field5,

…

From MySQLDatabase;

When I try this, I get an error of

Field not found - <Item>

CityItemCount:

     Mapping Load

          City,

          Count(Distinct Item)

                  Resident Table1

Can I not do a Count inside of a Mapping Load or is there some other syntax that has to be used?

[swuehl · Accepted solution]

Try

CityItemCount:

Mapping

LOAD

             City,

COUNT (DISTINCT Item)

Resident Table1

GROUP BY City;

[Anonymous]

Hi

As Stefan's reply

you have to write group by

Mapping

LOAD

             City,

COUNT (DISTINCT Item)

Resident Table1

GROUP BY City;

[dawgfather]

Thank you. I figured it was going to be something relatively simple that I had missing.

That worked.