Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

How to create 2x2 matrix table in qlikview?

  Hi,

I want to create a table structure like below with Age group and Gender where each data cell will display no of customer per age group and gender.

0-2021-3031-4041-5051-6060+
M233343536373
F121212121212
Company Owned114477110143176

This is my Customer table load script

[Customer]:

LOAD [Customer id],

     [Existing Customer],

     [Insertion Date],

     AGE,

     Gender,

     [Policy No]

FROM

(ooxml, embedded labels, table is Customer);

1 Solution

Accepted Solutions
Highlighted

Have a look at the attachment.

View solution in original post

14 Replies
Highlighted

Have a look at the attachment.

View solution in original post

Highlighted
Champion II
Champion II

Hi Anindya,

Please see attached QVW.

Use the below calculated dimension.

=if(AGE>=60,'60+',subfield(class(AGE,10),' ',1) &'-'& subfield(class(AGE,10),' ',5))

AgeGroup_Pivot.png

Cheers

V

Highlighted
Partner
Partner

tamilarasu Can u please send me a snapshot of the script and design.I am using Qlikview personal edition.

Highlighted
Champion II
Champion II

Or Change your Load Script to include AgeGroup and use that as your dimension.

[Customer]:

LOAD [Customer id],

     [Existing Customer],

     [Insertion Date],

     AGE,

          if(AGE>=60 , '60+',

               if(AGE>=50,'50-60',

                    if(AGE>=40,'40-50',

                             if(AGE>=30,'30-40',

                                   if(AGE>=20,'20-30','10-20')))) as AgeGroup,

     Gender,

     [Policy No]

FROM

(ooxml, embedded labels, table is Customer);

Highlighted
Master III
Master III

Instead of a huge nested IF-statement, I would suggest using the IntervalMatch() function instead.

http://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ScriptPrefixes/Interval...

Don't allow Perfection to be the enemy of Good Enough.
May you live in interesting times!
Highlighted

Hi

Try like this

Age:

Load *,

if(AGE<=20,'0-20',

if(AGE<=30,'21-30',

if(AGE<=40,'31-40',

if(AGE<=50,'41-50',

if(AGE<=60,'51-60','61+'))))) as "Age Bucket";

LOAD [Customer id],

     [Existing Customer],

     [Insertion Date],

     AGE,

     Gender,

     [Policy No]

FROM

[Cross table.xlsx]

(ooxml, embedded labels, table is [Sample data]);

In Front End:

Dimension: Gender and Age Bucket

Exp: Count(Customer Id)

Highlighted
Specialist III
Specialist III

What about using IntervalMatch:

Age_range:

LOAD * INLINE [

Start, End, Age_Range

0, 20, 0-20

21, 30, 21-30

31, 40, 31-40

41, 50, 41-50

51, 60, 51-60

61, 120, 61+

];

Data:

LOAD [Customer id],

     [Existing Customer],

     [Insertion Date],

     AGE,

     Gender,

     [Policy No]

FROM

(ooxml, embedded labels, table is [Sample data]);

IntervalMatch (AGE) LOAD Start, End Resident Age_range;

Results:

Screenshot_1.jpg

Highlighted

Anindya,

Data:

LOAD [Customer id],

     [Existing Customer],

     [Insertion Date],

     AGE,

     Gender,

     [Policy No],

     if(AGE>=0 and AGE<=20,'0-20',

  if(AGE>20 and AGE<=30,'21-30',

  if(AGE>30 and AGE<=40,'31-40',

  if(AGE>40 and AGE<=50,'41-50',

  if(AGE>50 and AGE<=60,'51-60','61+'))))) as Age_Bucket

FROM

(ooxml, embedded labels, table is [Sample data]);

Pivot table:

Dimensions:

Gender

Age_Bucket

Expression:

Count(Age_Bucket)

Capture.PNG

Highlighted
Partner
Partner

Hi,

tamilarasu I am getting this syructure as below.But how to transpose age column into row????

Capture.JPG