Data in Matrix

[Anonymous]

Hi,

I have a requirement to visualize the data coming from different systems and identify the difference between systems. At an overview level, I am trying to compare the count of records and sum of an amount column between multiple systems. Below is an example as how I would like to see:

  Data:

	Count	Amount
System 1	150	100000
System 2	120	90000
System 3	130	95000

This is how I would like to visualize the above data. Below is just based on count 

	System 1	System 2	System 3
System 1	0	30	20
System 2	30	0	10
System 3	20	10	0

Is there a way to achieve this?

Regards,

Dev

[sunny_talwar]

None of the two tables provided above seems to be raw data. This might be possible by creating another field in the script like this:

LOAD System,

          System as _System

          ...

FROM Source;

and use them both in your pivot table.

For a more help, you would need to provide a sample data behind this output.

[swuehl]

Assuming you are loading same field names from different tables, you can create a new field to identify your systems:

LOAD 'System 1' as System,

           Field1,

          Amount,

          OtherFields

FROM YourSystem1Table;

CONCATENATE

LOAD 'System 2' as System,

           Field1,

          Amount,

          OtherFields

FROM YourSystem2Table;

CONCATENATE

LOAD 'System 3' as System,

           Field1,

          Amount,

          OtherFields

FROM YourSystem3Table;

Then create a straight table chart with dimension System and two expressions

=Count(System)

=Sum(Amount)

I am not quite sure what you want to tell us with your second table.

[Anonymous]

Apologies if my requirement was not clear. First table is the data that shows the count of records per system and the second table is the output that I expect to see in adjacency matrix format:

	System 1	System 2	System 3
System 1	0	System 1.Count - System 2.count	System 1.count - System 3.count
System 2	System 1.Count - System 2.count	0	System 3.Count - System 2.count
System 3	System 1.count - System 3.count	System 3.Count - System 2.count	0

[Anonymous]

Thank you Sunny for your response. First table is the raw data and second table is the output I expect to see. Let me know if you need more info to assist.

[sunny_talwar]

I am still not sure I understand how am I going to create the required output from given input. How is 150 count for System 1 gets divided between System 2 and System 3?

[Anonymous]

Basically, I am doing this to reconciliate the data across systems. Data get transferred from System 1 to System 2 and from System 2 to System 3 and so on. In the process of transferring data from one system to another, it is expected to have the complete data transferred to the destination system without any reduction. So if there is any drop of record count from one system to another, then it has to be shown in the qlikview dashboard. I need to see variance in terms of count of records between systems.

[swuehl · Accepted solution]

Maybe something like this:

//Creating some sample data

INPUT:

LOAD Chr(ORD('A')+floor(RAND()*3)) as System,

  Recno() as ID

Autogenerate 20;

LOAD System as _System

RESIDENT INPUT;

Then using System and _System as dimensions and as expression

=Count(ID)-Count(DISTINCT TOTAL<_System> If(System=_System,ID))

System	_System	A	B	C
A		-	-3	-2
B		3	-	1
C		2	-1	-

System	count(ID)
	20
A	5
B	8
C	7

[Anonymous]

Thank you. This worked perfectly. I made some changes to what I need to show.