Swapping columns and rows

[nougatitati]

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?

[Channa]

swap column to row use 

crosstable(Avg,sales)

try to keep s1 ,s2 as second column and use generic load

[nougatitati]

---

@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?

[Channa · Accepted solution]

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

[nougatitati]

You're awesome! It worked!

Thanks so much, you've definitely saved me hours and hours of time.