2 Replies Latest reply: Aug 14, 2012 6:17 AM by thomas.whitaker RSS

    Crosstable \ Fill

      I have the following excel table:

       

       

      CountryRegionAge (0-20)Age (21-40)Age (40+)Average Age
      EnglandYorkshire20%10%70%39
      ScotlandGlasgow30%10%60%35
      IrelandBelfast40%20%40%29
      EnglandNorth East50%20%30%25

       

      I can now load the date using the following script to pivot the age group columns (omitting the average age)

       

      Tablename:

      Crosstable (Age, Proportion, 2)

      LOAD

      Country

      Region

      Age (0-20)

      Age (21-40)

      Age (40+)

      FROM file.xlax

       

      The table I would like to load would looks like this, where the average age fills in all of the relevent cells for the country \ region:

       

       

      CountryRegionAgeProportionAverage
      EnglandYorkshireAge (0-20)20%39
      EnglandYorkshireAge (21-40)10%39
      EnglandYorkshireAge (40+)70%39
      ScotlandGlasgowAge (0-20)30%35
      ScotlandGlasgowAge (21-40)10%35
      ScotlandGlasgowAge (40+)60%35
      IrelandBelfastAge (0-20)40%29
      IrelandBelfastAge (21-40)20%29
      IrelandBelfastAge (40+)40%29
      etc.