- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Complicated structure table
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
From ....
Where 1 = 1; // <<<<<<<
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Something like this??
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, not very clear how.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How have you done that???
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
PSA