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: 
Not applicable

Dynamic columns/header based on table entries

Hi,

I like to make a diagram dynamic based on values in a table. It is like a Pivot, but this does not matches to my requirement. Here an simple example of what I like to archive.

Table

                                                        

Item_ID | Item_type | Item_Value| Time

item 1   |  C2H6      |   23.45       | 01.01.2011

item 1   |  C2H6      |   24.67       | 01.02.2011

item 1   |  C2H6      |   31.25       | 01.03.2011

item 1   |  C6+        |     0.34       | 01.01.2011

item 1   |  C6+        |     0.47       | 01.02.2011

item 1   |  C6+        |     0,51       | 01.03.2011

item 1   |   N2         |    10.32      | 01.01.2011

.....

item 2   | C2H6       |   24.78       | 01.01.2011

......

Result should be:

Time          | Item_ID | C2H6 | C6+ | N2 ...

01.01.2011 | item 1   | 23.45 | 0.34 | 10.32

01.01.2011 | item 2   | 24.78 | ......

01.02.2011 | item 1   | 24.67 | 0.47 | ...

01.03.2011 | item 1   | 31.25 | 0.51 | ....

The Item_types are limited but not always predictable. So the column should be generated automaticlly. My idea was to get all item_type distinct. Making these values the headline. After this I need to put the number values of the item_type in that column.

Can anyone give me a glue or a solution?

Thanks in advance,

Danny Gerst

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Here is your solution

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

7 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    All you have to do is create a pivot table in qlikview from Layout -> Newsheet object -> chart -> click on pivot

    Assign the Dimension as Time, Item_ID, Item_type and expression as sum(Item_Value)

    After you click on finish.

    Drag Item_type column to the top.

    Hope this will help you

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Hi Kaushik,

thanks for your fast answer. While the values appears in a simple tablebox I miss them in the pivot table.

When I create a Pivot table only the first dimesion as headerline is shown (Time), but nothing else. It looks like this:

Pivot          Print Icon | Excel Icon | ...

Time

Thats all. Pivot is the name of the diagramm object. I simple assigned the dimensions and clicked on finish. This is strange I did not ran in the problem before. Do I misunderstand the Pivot Diagramm? The values are based on a simple table.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    Can you upload the qvw file.

    This will help us to understand the problem and find the solution.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
jonathandienst
Partner - Champion III
Partner - Champion III

Danny

You can achieve the result you specified with a pivot table, using a cross tabulation.

Create the pivot with Time, Item_ID and Item_type as dimensions, and Item_Value (or Sum(Item_Value) etc...) as the expression. Select the "Always Fully Expanded" option on the Presentation tab, and click OK. Click the header for Item_type and hold the mouse button down - you will see a blue vertical arrow line. Drag this to near the top right corner and it will convert to a horizontal blue arrow. Now release the mouse button.

Hope that helps.

Jonathan

Edit: pipped at the post by Kaushik

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi,

I attached the experimental file. Go to the tab "Arbeitsblatt 1". Exchange time -> Zeit, item_id -> EMRNR, item_type -> Spur, value -> Wert and you should be able to match this example to the concrete file.

Thanks for your help,

Danny

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Here is your solution

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Hi Kaushik,

thanks for your work and the solution. I appreached your effort which saved my day.

Best regards,

Danny