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

    Create a new field

    Jean-Jacques Jesua

      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
              Jean-Jacques Jesua

              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
                      Neil Miller

                      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
                          Jean-Jacques Jesua

                          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