topic Re: Create hierarchy into a field in QlikView

Create hierarchy into a field

Anonymous — Fri, 20 May 2016 10:00:52 GMT

Hello,

I have a field of different Notions and i want top create a hierarchy on it, like a + on the Pivot table.

Here my table:

Here the Notion named 'Final Reult Acc' is composed by all the other.

Do you have a idea please ?

Thanks in advance for your help,

Sébastien.

Re: Create hierarchy into a field

swuehl — Fri, 20 May 2016 10:56:54 GMT

If you want to have a tree view in your list box, you should create a field that is showing the hierarchy per node in a concatenated string with a delimiter, like

Root\

Root\Node1

Root\Node1\Node2\

Root\Node1\Node2\Node3

Root\Node1\Node2\Node4

Root\Node1\Node5\

Root\Node1\Node5\Node6

Then you can just enable the option for tree view in the list box properties general tab.

You can create this kind of field in the script using the HIERARCHY LOAD prefix, but you would need to create a table with adjacent nodes as input:

Hierarchies

Re: Create hierarchy into a field

Anonymous — Fri, 20 May 2016 11:53:54 GMT

Okay, i am going to check this .

But here i create the Table Notions into a a CrossTable, the notions are all fields which begin with [PNL_Notion/....]

How can i do that here please ?

Table:

CrossTable(Type_AIR, Value,15)

LOAD Date(LEFT(RIGHT('$(File)',15),10),'DD/MM/YYYY') as [AIR Import Data],

Ptf,

Sbu,

SbuLabel,

Pc,

Pclabel,

Ggop,

GgopLabel,

Gop,

GopLabel,

Ptf1,

PtfLabel,

Folder,

Source,

key,

[Pnl_Notion/Final Result Acc/EUR],

[Pnl_Notion/Final Result Acc/USD],

[Pnl_Notion/CFP-CMM/EUR],

[Pnl_Notion/CFP-CMM/USD],

[Pnl_Notion/E.D.M./EUR],

[Pnl_Notion/E.D.M./USD],

[Pnl_Notion/Net Trade Result/EUR],

[Pnl_Notion/Net Trade Result/USD],

[Pnl_Notion/Reallo CVA / DVA/EUR],

[Pnl_Notion/Reallo CVA / DVA/USD],

[Pnl_Notion/Retro Cooperation 2/EUR],

[Pnl_Notion/Retro Cooperation 2/USD],

[Pnl_Notion/SLS Final Result/EUR],

[Pnl_Notion/SLS Final Result/USD],

[Pnl_Notion/SMTD Impact/EUR],

[Pnl_Notion/SMTD Impact/USD],

[Pnl_Notion/SR IN TRD/EUR],

[Pnl_Notion/SR IN TRD/USD],

[Pnl_Notion/Sales Credits Newedge/EUR],

[Pnl_Notion/Sales Credits Newedge/USD]

FROM

[$(File)]

(ooxml, embedded labels, header is 1 lines, table is [Data AIR])

WHERE Pc <> 'COTJVE';

NEXT

FinalTable:

NoConcatenate

LOAD [AIR Import Data],

Ptf,

Sbu,

SbuLabel,

Pc,

Pclabel,

Ggop,

GgopLabel,

Gop,

GopLabel,

Ptf as Ptf2,

PtfLabel,

Folder,

Source,

key,

Left(Mid(Type_AIR, Index(Type_AIR, '/') + 1), Len(Mid(Type_AIR, Index(Type_AIR, '/') + 1)) - 4) as Type_AIR,

SubField(Type_AIR, '/', -1) as Currency,

Value

Resident Table

Where Value <> 0;

DROP Table Table;

Thanks in advance for your help,

Sébastien.

Re: Create hierarchy into a field

swuehl — Fri, 20 May 2016 11:56:50 GMT

Not sure how we can derive a hierarchy from this table.

Maybe just create manually an INLINE table with Type_AIR and the concatenated hierarchy string as shown above, to define the hierarchy in AIR_Type.