Adding dimension rows to Pivot Table chart dynamically?

[Alcarz]

I'm dealing with Hierarchies in Qlik Sense using the Hierarchy function (https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefix...). 

In this article you can see an example of the fields it generates: https://community.qlik.com/t5/Design/Unbalanced-n-level-hierarchies/ba-p/1474325

As you can see, on blue color, there are NodeN fields. So these fields in my case can be different in every script execution (can be more or less nodes) so I can not put a fixed number of these dimensions in the Pivot Table Dimensions Rows:

Is there any way of filling the Dimensions rows of the Pivot Table dynamically, given the existing nodes loaded in the script?

Thanks in advance.

[Aasir]

Try below

Define a variable that dynamically generates the dimensions based on the NodeN fields.

SET vDynamDimensions = 'Node1, Node2, Node3, Node4,..............'; // Adjust this based on your maximum expected nodes
 

In your Pivot Table, use the variable as dimensions dynamically

PivotTable:
LOAD
Field1,
Field2,
$(vDynamDimensions)
FROM ...

Since your NodeN fields can change in every script execution, you may need to update the variable dynamically based on the actual nodes loaded in the script. You can use Qlik Sense scripting and logic to identify and update the variable accordingly.

You might use a combination of scripting functions, like FieldNumber() or FieldIndex(), to dynamically determine the number of NodeN fields and update your variable.

[Alcarz]

Thanks for your ChatGPT answer but that's not what I'm looking for. The problem comes with the Pivot chart attributes, that have to be filled manually and I can't do that for my business requirement.

[vinieme12 · Accepted solution]

No dynamic way to add/remove the dimensions if they exist; but you can dynamically hide/show columns if they exist

If you expect a maximum of 15node levels then just create 15 columns as below

Dimensions

Node1 =$(=if(count(Node1),'Node1'))     & ShowColumnIf  Condition =if(count(Node1),1,0)

Node2 =$(=if(count(Node2),'Node2'))     & ShowColumnIf Condition =if(count(Node2),1,0)

.

. And so on

.

Node15 =$(=if(count(Node15),'Node15')) & ShowColumnIf Condition =if(count(Node15),1,0)

the column will show/hide in the pivot table if it exists

[Alcarz]

That makes sense 🙂 Not efficient enough but it's just how Qlik works I guess.

Thank you!