Crosstable \ Fill

[#1]

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.

[Nicole-Smith · Accepted solution]

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...

[#3]

Spot on, many thanks!