5 Replies Latest reply: Oct 5, 2010 5:55 PM by Jean-Jacques Jesua

# Create a new field

Hi,

I have a table with 3 fields :

Country

City

Population

I'd like to add a new field in my table , let's said Status where

Status = 'Biggest City' if the city is the biggest city within the country

Status = 'Other' if the city is not the biggest

Thanks for any help

Jean-Jacques

• ###### Create a new field

You can load from resident table......and make a condition.....

something like IF(Population>30000,'Biggest City','Other') AS Status

• ###### Create a new field

Not exactly, because I want to identify for each country the biggest one.

JJJ

• ###### Create a new field

i don´t now what is your rule....but if you put that expression in your load....you will identify each line(country)

• ###### Create a new field

Is there one record per city? If so, something like this should work:

`Countries:LOAD * INLINE [ Country, City, Population USA, Minneapolis, 10 USA, New York, 40 USA, Aimes, 1 Canada, Toronto, 20 Canada, Vancouver, 10];BiggestCities:LOAD Country, Max(Population) As MaxPopulationRESIDENT CountriesGROUP BY Country;DataInter:LOAD Country As tCountry, City as tCity, Population As tPopulationRESIDENT Countries;JOIN LOAD Country As tCountry, MaxPopulation As tMaxPopulationRESIDENT BiggestCities;DROP TABLE Countries;DROP TABLE BiggestCities;Data:LOAD tCountry As Country, tCity As City, tPopulation As Population, If(tPopulation = tMaxPopulation, 'Biggest City', 'Other') As StatusRESIDENT DataInter;DROP TABLE DataInter;`

That's probably not the most effecient way to do it, but it works. Replace the Inline load with however you are currently loading your data and the rest should work directly.

• ###### Create a new field

Thanks Nmiller,

I simplified your syntax using firstsortedvalue function

LEFT JOIN (Temp)
LOAD Country , firstsortedvalue(City , -Population ) as BiggestCity
RESIDENT Temp
Group By County ;

Data:

LOAD * , If(City=BiggestCity,'Biggest City', 'Other') as Status

Resident Temp;
Drop Table Temp;

JJJ