topic Create a new field in QlikView

Create a new field

Tue, 05 Oct 2010 20:23:39 GMT

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

Tue, 05 Oct 2010 20:36:56 GMT

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

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

Create a new field

Tue, 05 Oct 2010 20:41:12 GMT

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

JJJ

Create a new field

Tue, 05 Oct 2010 20:46:05 GMT

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

Tue, 05 Oct 2010 20:55:36 GMT

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.

Create a new field

Tue, 05 Oct 2010 21:55:02 GMT

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