Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
jakobjensen
Contributor II
Contributor II

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

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"

jakobjensen_0-1642532440312.png

 

what is the best way to do that?

 

3 Replies
chris_djih
Creator III
Creator III

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

If you found help, mark the correct answer and give some likes to ALL contributors, that tried to help.
jakobjensen
Contributor II
Contributor II
Author

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

chris_djih
Creator III
Creator III

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:
grafik.png

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

If you found help, mark the correct answer and give some likes to ALL contributors, that tried to help.