Dynamic pivot table with hierarchy

[#1]

Hi,

Does anyone know whether Qlikview supports the ability for dynamic pivot dimensions?

So by this I mean that I have a table which contains parent-child records within the same table

E.g.

ID     Name     ParentID    Sales

1      Dave       NULL         £50

2      James     1               £100

3      Rob         2               £100

4      Andy       2               £200

5      Don         4               £25

Now, I know that I can load this data structure in using a Hierarchy function, but this will give me a static number of columns. I will not the depth of the recursion at run time so I can't possibly know how many columns I'm going to have. Does Qlikview provide anyway to recursively load this list in and set up a pivot table with the Name field so that it doesn't need to know the names of the columns up front?

[Gysbert_Wassenaar]

No, Qlikview has no way to know at load time which fields (of all loaded fields) you want in a particular pivot table. The easiest way around this is to create an empty table with enough fields so your data will always fit. Then load your data and apply the hierarchy function and append the resulting data to the empty table that was created earlier.

Another way could be use the Depth parameter with the Hierarchy function, find the maximum depth value and store it in a variable. Then you could create a macro that creates a pivot table with all the necessary fields using the value in the variable as a counter for a loop that adds the fields to the pivot table. If you want to try that, you're on your own