Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table that looks like this:
Subject | Avg1 | Avg2 | Avg3 | Avg4 |
s1 | a1 | b1 | c1 | d1 |
s2 | a2 | b2 | c2 | d2 |
s3 | a3 | b3 | c3 | d3 |
... | ... | ... | ... | ... |
But I want to make a visualisation that compares averages against each other, i.e. using 'average context' as a dimension, where each 'context' is just avg1, avg2, avg3, avg4. To this end, I think I need a table that looks like this:
Context | s1 | s2 | s3 | ... |
Avg1 | a1 | a2 | a3 | ... |
Avg2 | b1 | b2 | b3 | ... |
Avg3 | c1 | c2 | c3 | ... |
Avg4 | d1 | d2 | d3 | ... |
I thought I should use the CrossTable keyword, but I don't know how to get it working properly; I get errors regarding data connections et cetera when I have these tables already loaded in other places. How can I get the table layout I need?
EDIT: I found this and this about the so-called Generic Load. I tried this, but it had some bizarre side effects (it created several tables and did not have all the rows in fields as I had hoped).
SwappedRowColumnTable:
Generic Load * Resident OriginalTable;
Is this the right idea? How can I get it working?
AvgTable:
Load
[Subject],
Interval(Avg([timeelapsed1]), 'hh:mm:ss') as [Avg time1],
Interval(Avg([timeelapsed2]), 'hh:mm:ss') as [Avg time2],
Interval(Avg([timeelapsed3]), 'hh:mm:ss') as [Avg time3]
Resident TimeElapsedTable
Group by [Subject];
CrossTable(AVG, TIME)
LOAD Subject,AvgTime1,AvgTime2,AvgTime3 resident AvgTable
if this dosent work try to load previous table into QVD instead of resident load from QVD
swap column to row use
crosstable(Avg,sales)
try to keep s1 ,s2 as second column and use generic load
@Channa wrote:swap column to row use
crosstable(Avg,sales)
try to keep s1 ,s2 as second column and use generic load
Sorry I don't understand what you mean at all. What do you mean by crosstable(Avg,Sales)? Do you mean subject?
Maybe it'll help if I give some more sample stuff.
My data load looks something like this:
AvgTable:
Load
[Subject],
Interval(Avg([timeelapsed1]), 'hh:mm:ss') as [Avg time1],
Interval(Avg([timeelapsed2]), 'hh:mm:ss') as [Avg time2],
Interval(Avg([timeelapsed3]), 'hh:mm:ss') as [Avg time3]
Resident TimeElapsedTable
Group by [Subject];
Do you mean I should do something like this?
SwappedTable:
CrossTable(Avg,Subject)
LOAD *
Resident AvgTable;
Because this returns an error, saying I have to have unique field names. I can verify that the field [Subject] is entirely unique across the whole of AvgTable, so I'm confused as to why this is happening. For reference, here is the general form of my table, AvgTable:
I'm not sure what you meant by "try to keep s1 ,s2 as second column and use generic load" either. Can you help me with some sample syntax for crosstable and generic load in this case?
AvgTable:
Load
[Subject],
Interval(Avg([timeelapsed1]), 'hh:mm:ss') as [Avg time1],
Interval(Avg([timeelapsed2]), 'hh:mm:ss') as [Avg time2],
Interval(Avg([timeelapsed3]), 'hh:mm:ss') as [Avg time3]
Resident TimeElapsedTable
Group by [Subject];
CrossTable(AVG, TIME)
LOAD Subject,AvgTime1,AvgTime2,AvgTime3 resident AvgTable
if this dosent work try to load previous table into QVD instead of resident load from QVD
You're awesome! It worked!
Thanks so much, you've definitely saved me hours and hours of time.