Complicated structure table

Leo2 — Sat, 16 Nov 2024 03:48:57 GMT

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!

Re: Complicated structure table

lfetensini — Wed, 24 Apr 2019 17:10:14 GMT

Add in your load:

From ....
Where 1 = 1; // <<<<<<<

Re: Complicated structure table

Qrishna — Wed, 24 Apr 2019 20:37:30 GMT

Something like this??

Re: Complicated structure table

Leo2 — Mon, 29 Apr 2019 11:37:06 GMT

Sorry, not very clear how.

Re: Complicated structure table

Leo2 — Mon, 29 Apr 2019 11:37:31 GMT

How have you done that???

Re: Complicated structure table

Qrishna — Tue, 30 Apr 2019 19:14:14 GMT

PSA

topic Re: Complicated structure table in QlikView

Complicated structure table

Re: Complicated structure table

Re: Complicated structure table

Re: Complicated structure table

Re: Complicated structure table

Re: Complicated structure table