topic Dynamic columns/header based on table entries in QlikView

Dynamic columns/header based on table entries

Fri, 17 Jun 2011 09:08:52 GMT

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

Dynamic columns/header based on table entries

kaushiknsolanki — Fri, 17 Jun 2011 09:17:43 GMT

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

Dynamic columns/header based on table entries

Fri, 17 Jun 2011 09:33:54 GMT

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.

Dynamic columns/header based on table entries

kaushiknsolanki — Fri, 17 Jun 2011 09:35:35 GMT

Hi,

Can you upload the qvw file.

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

Regards,

Kaushik Solanki

Re: Dynamic columns/header based on table entries

jonathandienst — Fri, 17 Jun 2011 09:37:31 GMT

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

Dynamic columns/header based on table entries

Fri, 17 Jun 2011 09:50:20 GMT

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

Re: Dynamic columns/header based on table entries

kaushiknsolanki — Fri, 17 Jun 2011 10:06:42 GMT

Hi,

Here is your solution

Regards,

Kaushik Solanki

Dynamic columns/header based on table entries

Fri, 17 Jun 2011 11:17:20 GMT

Hi Kaushik,

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

Best regards,

Danny