Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cfarquharson
Contributor II
Contributor II

Can you make a Straight Table horizontal in Qlik Sense

Hi there,

I have a requirement to list a number of measures in a table, horizontally. I have attached a picture as an example of my table, it consists of 12 measures listed vertically, which looks ridiculous. Listing them horizontally will be more space efficient. 

In QlikView I was able to convert straight Tables from Vertical to Horizontal, is this no longer possible in Qlik Sense? Is there an extension out that will allow me to do this?

Labels (4)
6 Replies
aivislau
Partner - Contributor
Partner - Contributor

cfarquharson
Contributor II
Contributor II
Author

The problem is that I am not using any dimensions. A pivot table requires a dimension.

aivislau
Partner - Contributor
Partner - Contributor

A simple workaround is that you can create some "dummy" dimension in the pivot table and name it, for example, as "Values", "EUR" or whatever.

cfarquharson
Contributor II
Contributor II
Author

I can't see how this can solve my problem. This describes my scenario better:

I have measures listed in a table and I need to transpose this.

cfarquharson_2-1588874095700.png

This is possible in QlikView as you can create tables with no dimensions.

cfarquharson_0-1588874039507.png

I require an equivalent in Qlik Sense.

 

ArnadoSandoval
Specialist II
Specialist II

Hi @cfarquharson 

Yes, you can do it with QlikSense's load script; I am not aware of any Chart object able to do the transposition; with the load script you will be using:

  1. Generic (QlikSense Help) 
  2. Generic (QlikView Help) this help page include an example illustrating how the Generic prefix works.
  3. How to transpose rows to columns  about a year ago, in March-2019 somebody asked a similar question.
  4. The Generic Load (this post was quoted as a reference for the March-2019; it was written in 2014, it answers with more details how to implement the Generic prefix and transpose your table's row into columns.

I will share my own notes, as I played with your data and the information gathered from the posts-helps referenced above:

  • You need a table with your MonthlySummary, the one you included in your reply, it could be an Excel or CSV file.
  • Your Monthly-Summary table follows an structure known as key-value (very popular), each row represent a key-value combination, but unfortunately the Generic help page speak about a key in a different context, so, I will write property-value instead.
  • The Generic prefix expect these property-value to belong to a group, basically because it expect 3 columns: key-attribute-value; I hard coded all your property-value to Group 1 in the load script, as shown below
Measures:
Generic Load 1 As Group, MonthySummary, Value Inline [
MonthySummary, Value
Measure 1, 11
Measure 2, 12
Measure 3, 13
Measure 4, 14
Measure 5, 15
Measure 6, 16
Measure 7, 17
Measure 8, 18
Measure 9, 19
Measure 10, 110
Measure 11, 111
Measure 12, 112
];
  •  Notice the 1 As Group just after the load, there is no need to modify your data; but the implication is great, if you are dealing with multi-companies, instead of group, it could be a Company-ID instead and be part of your data, preventing the need of adding a dummy column.
  • The Generic Load create as many tables as rows in the data we are Loading, as your example contains 12 rows, it generates 12 tables.
  • The names of these tables are [table-name].[property-name]; I used Measures as the table name, so I ended up with 12 tables with these names: Measures.Measure 1; Measure.Measures 2; ... ; Measures.Measure 12; these tables contain two columns group and property-name; so for the first table: Measures.Measure 1, its two columns are Group and Measure 1; for the next table Measures.Measure 2 its two columns are Group and Measure 2, and so on. Notice that all these 12 tables share the column Group, the first parameter appearing on the Load and the we forced to 1; also each one of these tables have a single row.
  • Now this partial result looks very messy, 12 tables and we want just one table, that's when the post referenced on the link above saved the day, as it contains a block of code that join all these 12 tables into 1, with as many columns as properties. (You need to visit that post)
  • There are a couple of things you need to know before using the code you are about to find, they are highlighted below:

BlockOfCode_01.jpg

and

BlockOfCode_02.jpg

And this is the result table screenshot:

Results.jpg

Hope this helps

 

 

 

 

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
RGrafrath
Contributor
Contributor

I have accomplished this using the ValueList function, without modifying my load script (which was complex enough as it is).

1) Create a table

2) Add a Dimension using the expression editor. Use the ValueList() function to make a list of the measure names you want listed in your table. Name this dimension something like "Measure". Example of this dimension:

       RGrafrath_0-1689722729521.png

3) Add a Measure using the expression editor. This is where it gets ugly. You'll make a nested "if" statement for each measure, and you'll put all of your calculations in this statement. Make sure to use formatting functions, since this is a single field which can't be formatted elsewhere. Name the field something like "Value". Example:

RGrafrath_1-1689723056041.png

Results:

RGrafrath_2-1689723265029.png