Hello there. I've got some crosstable with sophisticated attribute structure (I'll better show the actual table then explain (see excel file)). I've tried to load that using GENERIC parameter, but the system just makes hundreds of separate tables, which is not what I really need. Tried to join them with some script but without much success.
I've also tried another thing. Written a script:
MortalityIndicatorsByGender_temp:
CrossTable(Attributes, Data, 1)
LOAD
// F1 as Region,
Country,
imr95,
mimr95,
fimr95,
mort1to495,
mmort1to495,
fmort1to495,
mortlt595,
mmortlt595,
fmortlt595,
life95,
mlife95,
flife95,
imr96,
mimr96,
fimr96,
mort1to496,
mmort1to496,
fmort1to496,
mortlt596,
mmortlt596,
fmortlt596,
life96,
mlife96,
flife96,
imr97,
mimr97,
fimr97,
mort1to497,
mmort1to497,
fmort1to497,
mortlt597,
mmortlt597,
fmortlt597,
life97,
mlife97,
flife97,
imr98,
mimr98,
fimr98,
mort1to498,
mmort1to498,
fmort1to498,
mortlt598,
mmortlt598,
fmortlt598,
life98,
mlife98,
flife98,
imr99,
mimr99,
fimr99,
mort1to499,
mmort1to499,
fmort1to499,
mortlt599,
mmortlt599,
fmortlt599,
life99,
mlife99,
flife99,
imr00,
mimr00,
fimr00,
mort1to400,
mmort1to400,
fmort1to400,
mortlt500,
mmortlt500,
fmortlt500,
life00,
mlife00,
flife00,
imr01,
mimr01,
fimr01,
mort1to401,
mmort1to401,
fmort1to401,
mortlt501,
mmortlt501,
fmortlt501,
life01,
mlife01,
flife01,
imr02,
mimr02,
fimr02,
mort1to402,
mmort1to402,
fmort1to402,
mortlt502,
mmortlt502,
fmortlt502,
life02,
mlife02,
flife02,
imr03,
mimr03,
fimr03,
mort1to403,
mmort1to403,
fmort1to403,
mortlt503,
mmortlt503,
fmortlt503,
life03,
mlife03,
flife03,
imr04,
mimr04,
fimr04,
mort1to404,
mmort1to404,
fmort1to404,
mortlt504,
mmortlt504,
fmortlt504,
life04,
mlife04,
flife04,
imr05,
mimr05,
fimr05,
mort1to405,
mmort1to405,
fmort1to405,
mortlt505,
mmortlt505,
fmortlt505,
life05,
mlife05,
flife05,
imr06,
mimr06,
fimr06,
mort1to406,
mmort1to406,
fmort1to406,
mortlt506,
mmortlt506,
fmortlt506,
life06,
mlife06,
flife06,
imr07,
mimr07,
fimr07,
mort1to407,
mmort1to407,
fmort1to407,
mortlt507,
mmortlt507,
fmortlt507,
life07,
mlife07,
flife07,
imr08,
mimr08,
fimr08,
mort1to408,
mmort1to408,
fmort1to408,
mortlt508,
mmortlt508,
fmortlt508,
life08,
mlife08,
flife08,
imr09,
mimr09,
fimr09,
mort1to409,
mmort1to409,
fmort1to409,
mortlt509,
mmortlt509,
fmortlt509,
life09,
mlife09,
flife09,
imr10,
mimr10,
fimr10,
mort1to410,
mmort1to410,
fmort1to410,
mortlt510,
mmortlt510,
fmortlt510,
life10,
mlife10,
flife10,
imr11,
mimr11,
fimr11,
mort1to411,
mmort1to411,
fmort1to411,
mortlt511,
mmortlt511,
fmortlt511,
life11,
mlife11,
flife11,
imr12,
mimr12,
fimr12,
mort1to412,
mmort1to412,
fmort1to412,
mortlt512,
mmortlt512,
fmortlt512,
life12,
mlife12,
flife12
FROM
SourceData\MortalityIndicatorsByGender.xlsx
(ooxml, embedded labels, header is 2 lines, table is data);
STORE MortalityIndicatorsByGender_temp INTO 'Stored\mortality.qvd' (qvd);
DROP Table MortalityIndicatorsByGender_temp;
inner join
LOAD Country,
//Attributes,
//Data,
If(
right(Attributes, 2)>90,
19&right(Attributes, 2),
20&right(Attributes, 2)
)
as Year,
If(
Left(Attributes, 3)='imr',
Data
)
as InfantMortBoth,
If(
Left(Attributes, 4)='mimr',
Data
)
as InfantMortMale,
If(
Left(Attributes, 4)='fimr',
Data
)
as InfantMortFem,
If(
Left(Attributes, 8)='mort1to4',
Data
)
as InfantMort1to4Both,
If(
Left(Attributes, 9)='mmort1to4',
Data
)
as InfantMort1to4Male,
If(
Left(Attributes, 9)='fmort1to4',
Data
)
as InfantMort1to4Fem,
If(
Left(Attributes, 7)='mortlt5',
Data
)
as InfantMortUnder5Both,
If(
Left(Attributes, 8)='mmortlt5',
Data
)
as InfantMortUnder5Male,
If(
Left(Attributes, 8)='fmortlt5',
Data
)
as InfantMortUnder5Fem,
If(
Left(Attributes, 4)='life',
Data
)
as LifeExpBoth,
If(
Left(Attributes, 5)='mlife',
Data
)
as LifeExpMale,
If(
Left(Attributes, 5)='flife',
Data
)
as LifeExpFem
FROM
Stored\mortality.qvd
(qvd);
But what I've got in the end is table with a lot of empty cells (see the picture attached). I'm wondering is there any chance to "compact" that table so there is only one "Year" record corresponding with the rest of dimensions? Hopefully it's somewhat clear, what I'm trying to say... Or any other ideas, guys?
Many thanks!
