Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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.
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.
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 |
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.
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?
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.
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 |
Thank you. This worked perfectly. I made some changes to what I need to show.