Crosstable \ Fill

Fri, 10 Aug 2012 14:42:16 GMT

I have the following excel table:

Country	Region	Age (0-20)	Age (21-40)	Age (40+)	Average Age
England	Yorkshire	20%	10%	70%	39
Scotland	Glasgow	30%	10%	60%	35
Ireland	Belfast	40%	20%	40%	29
England	North East	50%	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:

Country	Region	Age	Proportion	Average
England	Yorkshire	Age (0-20)	20%	39
England	Yorkshire	Age (21-40)	10%	39
England	Yorkshire	Age (40+)	70%	39
Scotland	Glasgow	Age (0-20)	30%	35
Scotland	Glasgow	Age (21-40)	10%	35
Scotland	Glasgow	Age (40+)	60%	35
Ireland	Belfast	Age (0-20)	40%	29
Ireland	Belfast	Age (21-40)	20%	29
Ireland	Belfast	Age (40+)	40%	29
etc.

Re: Crosstable \ Fill

Nicole-Smith — Fri, 10 Aug 2012 15:38:01 GMT

Why not just do this?

Tablename:

Crosstable (Age, Proportion, 3)

LOAD

Country,

Region,

Average,

Age (0-20),

Age (21-40),

Age (40+)

FROM file.xlax

The columns don't need to be loaded in the same order that they are in the Excel file...

Tue, 14 Aug 2012 10:17:04 GMT

Spot on, many thanks!