Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can load from resident table......and make a condition.....
something like IF(Population>30000,'Biggest City','Other') AS Status
Not exactly, because I want to identify for each country the biggest one.
JJJ
i don´t now what is your rule....but if you put that expression in your load....you will identify each line(country)
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 MaxPopulation
RESIDENT Countries
GROUP BY Country;
DataInter:
LOAD Country As tCountry,
City as tCity,
Population As tPopulation
RESIDENT Countries;
JOIN LOAD Country As tCountry, MaxPopulation As tMaxPopulation
RESIDENT 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 Status
RESIDENT 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.
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