Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
anindya_manna
Creator II
Creator II

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
tamilarasu
Champion
Champion

Have a look at the attachment.

View solution in original post

14 Replies
tamilarasu
Champion
Champion

Have a look at the attachment.

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
anindya_manna
Creator II
Creator II
Author

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

vinieme12
Champion III
Champion III

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);

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
oknotsen
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...

May you live in interesting times!
MayilVahanan

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)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
MindaugasBacius
Partner - Specialist III
Partner - 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

tamilarasu
Champion
Champion

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

anindya_manna
Creator II
Creator II
Author

Hi,

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

Capture.JPG