Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
Mr_small_t
New Contributor

Using the same dimension for both axis in a pivot "transforamation matrix"

Hi

I have a table as shown below with information of a students id (Stid), the schoolyear (Schoolyear), name of the school (School) and grade/level (grade). 

I need to create a "transformation matrix"/pivot table which illustrates to what degree students changes school between the schoolyear. I.e. Along the vertical axis in a pivot table i need to have the school of choice for "2. graders" in the schoolyear of 20172018, and along the horisontal axis in the pivot table i need to have the school of choice for the "3. graders" in the schoolyear of 20182019. The fields in the table should be populated with the count of disctinct student id numbers. 

I have tried to understand other crosstable-discussions, but I have not been able to figure out how to do this. I hope someone can help me.

 

LOAD * INLINE [
Stid, Schoolyear, School, grade
1234, 20162017, School A, 1
1234, 20172018, School A, 2
1234, 20182019, School A, 3
2234, 20162017, School B, 1
2234, 20172018, School B, 2
2234, 20182019, School B, 3
3234, 20162017, School C, 1
3234, 20172018, School C, 2
3234, 20182019, School C, 3
4234, 20162017, School C, 1
4234, 20172018, School A, 2
4234, 20182019, School A, 3
5234, 20162017, School C, 1
5234, 20172018, School A, 2
5234, 20182019, School A, 3
6234, 20162017, School C, 1
6234, 20172018, School B, 2
6234, 20182019, School A, 3
7234, 20162017, School A, 1
7234, 20172018, School B, 2
7234, 20182019, School A, 3
8234, 20172018, School B, 1
8234, 20182019, School A, 2
9234, 20172018, School B, 1
9234, 20182019, School B, 2
10234, 20172018, School B, 1
10234, 20182019, School C, 2
11234, 20172018, School A, 1
11234, 20182019, School A, 2
12234, 20172018, School A, 1
12234, 20182019, School C, 2
13234, 20172018, School B, 1
13234, 20182019, School C, 2
];

 

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Using the same dimension for both axis in a pivot "transforamation matrix"

Add a Data: label to the inline load and the following after the inline load:

Let vM2 = 20172018;
Let vM3 = 20182019;
 
T1:
LOAD 
	Stid,
	School as S2
Resident Data Where grade = 2 and Schoolyear = $(vM2);

Join (T1)
LOAD 
	Stid,
	School as S3
Resident Data Where grade = 3 and Schoolyear = $(vM3);

Next, create a pivot table using S2 and S3 as dimensions, and count(Stid) as the expression. Drag one of the dimensions to the top to create a cross-table. See attached.

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
2 Replies
MVP
MVP

Re: Using the same dimension for both axis in a pivot "transforamation matrix"

Add a Data: label to the inline load and the following after the inline load:

Let vM2 = 20172018;
Let vM3 = 20182019;
 
T1:
LOAD 
	Stid,
	School as S2
Resident Data Where grade = 2 and Schoolyear = $(vM2);

Join (T1)
LOAD 
	Stid,
	School as S3
Resident Data Where grade = 3 and Schoolyear = $(vM3);

Next, create a pivot table using S2 and S3 as dimensions, and count(Stid) as the expression. Drag one of the dimensions to the top to create a cross-table. See attached.

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Mr_small_t
New Contributor

Re: Using the same dimension for both axis in a pivot "transforamation matrix"

Hi

thanks for the swift and very helpfuel reply!

 

Regards,

Torbjørn

Community Browser