topic Re: Matrix Straight Table in QlikView

Matrix Straight Table

asknyldz — Mon, 16 Mar 2015 15:29:27 GMT

Hi there,

I need a Matrix Straight Table.

My data;

Data	Amount
x	100
y	120
z	150

I need below Straight Table

Horizontal : Divisor

Vertical: Divided By

	x	y	z
x	100%	83%	100%
y	120%	100%	80%
z	150%	125%	100%

Note: Needed only text, i can't open Qlikview files, i don't yet have licenced version.

Thanks

Re: Matrix Straight Table

rubenmarin — Mon, 16 Mar 2015 15:39:46 GMT

Hi Askin, you'll need to duplicate and mix all the values, you can do that with a join:

Data:

LOAD * Inline [

Data,Amount

x,100

y,120

z,150

];

Join (Data) LOAD Data as DataV, Amount as AmountV Resident Data;

* Next you can create a Pivot table (straight table can't use vertical dimension)

Use Data and DataV as dimensions dragging DataV as vertical dimension (you can do that creting an expression with value =1)

Create this expression:

Amount/AmountV

This will return a table like this:

Re: Matrix Straight Table

MK_QSL — Mon, 16 Mar 2015 15:50:10 GMT

Use below script..

=================

T1:

Load * Inline

[

Data, Amount

x, 100

y, 120

z, 150

];

T2:

Load Distinct Data, Amount Resident T1;

Join

Load Distinct Data as Data1, Amount as Amount1 Resident T1;

=============================================

Now create a Pivot Table

Dimension

Data

Data1

Expression

NUM(SUM(Amount)/SUM(Amount1),'0.00%')

Re: Matrix Straight Table

MK_QSL — Mon, 16 Mar 2015 15:51:01 GMT

Ruben, you are little quicker than me...

Re: Matrix Straight Table

rubenmarin — Mon, 16 Mar 2015 15:57:07 GMT

I was doing a big reload and had plenty of free time, refreshing content of forum looking for a zero-answers question. Since I discovered community I can't just wait to finish reloads without visiting the forum.