Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Leo2
New Contributor II

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! 

Labels (2)
5 Replies
lftensini
New Contributor III

Re: Complicated structure table

Add in your load:

From ....
Where 1 = 1; // <<<<<<<
krishna_2644
Valued Contributor III

Re: Complicated structure table

Something like this??

1571019 - Cross Table snip.PNG

Leo2
New Contributor II

Re: Complicated structure table

Sorry, not very clear how.

Leo2
New Contributor II

Re: Complicated structure table

How have you done that???

krishna_2644
Valued Contributor III

Re: Complicated structure table

PSA