topic Re: Create bar-plot/count-plot when having one row that contains aggregated data in App Development

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

jakobjensen — Tue, 18 Jan 2022 19:11:22 GMT

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?

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

chris_djih — Wed, 19 Jan 2022 12:45:56 GMT

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

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

jakobjensen — Thu, 20 Jan 2022 07:53:15 GMT

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

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

chris_djih — Thu, 20 Jan 2022 09:51:39 GMT

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