Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

5 Replies
Not applicable
Author

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

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

Not applicable
Author

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

JJJ

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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