Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Crosstable \ Fill

I have the following excel table:

CountryRegionAge (0-20)Age (21-40)Age (40+)Average Age
EnglandYorkshire20%10%70%39
ScotlandGlasgow30%10%60%35
IrelandBelfast40%20%40%29
EnglandNorth East50%20%30%25

I can now load the date using the following script to pivot the age group columns (omitting the average age)

Tablename:

Crosstable (Age, Proportion, 2)

LOAD

Country

Region

Age (0-20)

Age (21-40)

Age (40+)

FROM file.xlax

The table I would like to load would looks like this, where the average age fills in all of the relevent cells for the country \ region:

CountryRegionAgeProportionAverage
EnglandYorkshireAge (0-20)20%39
EnglandYorkshireAge (21-40)10%39
EnglandYorkshireAge (40+)70%39
ScotlandGlasgowAge (0-20)30%35
ScotlandGlasgowAge (21-40)10%35
ScotlandGlasgowAge (40+)60%35
IrelandBelfastAge (0-20)40%29
IrelandBelfastAge (21-40)20%29
IrelandBelfastAge (40+)40%29
etc.



1 Solution

Accepted Solutions

Re: Crosstable \ Fill

Why not just do this?

Tablename:

Crosstable (Age, Proportion, 3)

LOAD

Country,

Region,

Average,

Age (0-20),

Age (21-40),

Age (40+)

FROM file.xlax

The columns don't need to be loaded in the same order that they are in the Excel file...

2 Replies

Re: Crosstable \ Fill

Why not just do this?

Tablename:

Crosstable (Age, Proportion, 3)

LOAD

Country,

Region,

Average,

Age (0-20),

Age (21-40),

Age (40+)

FROM file.xlax

The columns don't need to be loaded in the same order that they are in the Excel file...

Highlighted
Not applicable

Re: Crosstable \ Fill

Spot on, many thanks!