topic Re: How to create 2x2 matrix table in qlikview? in QlikView

How to create 2x2 matrix table in qlikview?

anindya_manna — Wed, 25 Nov 2020 16:16:04 GMT

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-20	21-30	31-40	41-50	51-60	60+
M	23	33	43	53	63	73
F	12	12	12	12	12	12
Company Owned	11	44	77	110	143	176

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

Re: How to create 2x2 matrix table in qlikview?

tamilarasu — Mon, 04 Jul 2016 09:34:47 GMT

Have a look at the attachment.

Re: How to create 2x2 matrix table in qlikview?

vinieme12 — Mon, 04 Jul 2016 09:37:17 GMT

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

Cheers

Re: How to create 2x2 matrix table in qlikview?

anindya_manna — Mon, 04 Jul 2016 09:41:32 GMT

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

Re: How to create 2x2 matrix table in qlikview?

vinieme12 — Mon, 04 Jul 2016 09:42:28 GMT

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

Re: How to create 2x2 matrix table in qlikview?

oknotsen — Mon, 04 Jul 2016 09:53:06 GMT

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/IntervalMatch.htm‌

Re: How to create 2x2 matrix table in qlikview?

MayilVahanan — Mon, 04 Jul 2016 09:53:45 GMT

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)

Re: How to create 2x2 matrix table in qlikview?

MindaugasBacius — Mon, 04 Jul 2016 09:55:24 GMT

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:

Re: How to create 2x2 matrix table in qlikview?

tamilarasu — Mon, 04 Jul 2016 10:04:07 GMT

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)

Re: How to create 2x2 matrix table in qlikview?

anindya_manna — Mon, 04 Jul 2016 10:26:01 GMT

Hi,

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

Re: How to create 2x2 matrix table in qlikview?

oknotsen — Mon, 04 Jul 2016 10:54:24 GMT

Make sure it is a Pivot Table.

Click on "Age" and keep your mouse button pressed.

Move your mouse to the top-right corner. An arrow will show up.

You might have to make some small circles with your mouse before the arrow points in the right direction (to the left instead of down).

Let go of your mouse and, tada, columns .

Re: How to create 2x2 matrix table in qlikview?

MindaugasBacius — Mon, 04 Jul 2016 11:09:41 GMT

Select the column and use principle drag and drop.

Re: How to create 2x2 matrix table in qlikview?

tamilarasu — Mon, 04 Jul 2016 12:04:27 GMT

Hi Anindya,

You can follow the steps mentioned by Onno. Kindly let us know, if you have any issues.

Re: How to create 2x2 matrix table in qlikview?

anindya_manna — Mon, 04 Jul 2016 12:25:39 GMT

Hello,

Thank you very much for your reply.Finally I am able to do it.Actually I have unchecked allow pivoting option in presentation tab of pivot table.

this vedio is also good

QlikView | Pivot Tables - YouTube

Re: How to create 2x2 matrix table in qlikview?

oknotsen — Mon, 04 Jul 2016 12:58:53 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .