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