Create bar-plot/count-plot when having one row that contains aggregated data

[jakobjensen]

I have a table (from a SQL-table) looking like this

#my_table

a1 a2  a3  b1  b2  c0  c1 c2 c3
--+--+----+---+---+---+--+--+--
20 15  10 18.5 13  2   0  23  30

 and I want to make a barchart of the "a" the "b" and the "c" groups i.e three different bar-plots where the y-axis is the value, and the x-axis is the name e.g for the "a"

what is the best way to do that?

[chris_djih]

If I understand it correctly you want to group your first dimension by the first character?

there aseveral possible solutions: assumed your first field (a1,a2,b1,...)  is called "Dim1" and your second line (20,15,10,...) ist calles "Measure1"

1) create a new Field: Left(Dim1,1) as Group1  //takes the first character (from left)

2) if your data model is more complex and you cannot use the first letter try SubField() or Wildmatch() -> read the onlin help for further information

[jakobjensen]

No, the `a1`,`a2`,`a3` etc are the dimensions/columns of my table

[chris_djih]

Ok, then you may want to try the crosstable transformation:
the columns will be transformed into one Dimension with their respective assigned value as a new measure-Field.

// First we need a leading column (e.g. a date marker)
myNewTable:
Load *,
	today() as date
resident myTable;

//then we transform all the other columns into one dimension
myCrosstable:
//the "1" defines how many leading fields are fixed (1-> date).
//"columns" is the name of the new created dimension
//"Value" is the field were the former vlaues land in.
Crosstable([Columns], [Value], 1) 
LOAD
	[date],/* need 1 dim that stays*/ 
	[a1],[a2],[a3],[b1],[b2],[c0],[c1],[c2] 
Resident myNewTable; 

//To fullfill your tast of Gorups, just create a new field.
finalTable:
Load *,
	left(Columns,1)	as Group
Resident myCrosstable;

//CleanUp table to avoid synth. Keys
drop tables myCrosstable,myTable,myNewTable;

final table looks like this:

Now you can easily make a bar chart with "Group" as dimension and "Sum(Value)" as Measure.
or if you just want the a's in one chart: "Columns" as dimension and "Sum( {<Group={'a'}>} Value)" as Measure