Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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
Highlighted
Partner
Partner

Add in your load:

From ....
Where 1 = 1; // <<<<<<<
Support your colleagues. Remember to "like" the answers that are helpful to you and flag as "solved" the one that helped you solve. Cheers.
Highlighted
Specialist III
Specialist III

Something like this??

1571019 - Cross Table snip.PNG

Highlighted
Contributor III
Contributor III

Sorry, not very clear how.

Highlighted
Contributor III
Contributor III

How have you done that???

Highlighted
Specialist III
Specialist III

PSA