topic Hierarchy in QlikView

Hierarchy

pauldamen — Mon, 25 Jan 2016 13:48:10 GMT

Hi all,

I have 2 columns in an excel, one with a number and one with a name. Looks like this:

Number Name

1 G1000.F01

1.1 G1000.F03

1.1.1 G1000.G02

1.1.2 G1000.G03

1.1.3 G1000.G04

1.1.4 G1000.G05

How can I create a hierarchy with the numbers from column 1 and have the drill down in the listbox based on the names in 2?

Regards, Paul

Re: Hierarchy

ogster1974 — Mon, 25 Jan 2016 14:02:54 GMT

just add records to your table to build the hierarchy for your example if name G1000.F01 had a new number 1.1 add like so

Number Name

1 G1000.F01

1.1 G1000.F03

1.1.1 G1000.G02

1.1.2 G1000.G03

1.1.3 G1000.G04

1.1.4 G1000.G05

1.1 G1000.F01

That way if you filtered on G1000.F01 in a list box it would return 1 and 1.1

If you want it more complex adding a Parent Column to your table would allow you to build more relationships.

Re: Hierarchy

pauldamen — Mon, 25 Jan 2016 14:10:04 GMT

Hi,

Thanks I probably need it more complex. Already thought about adding the parent in a column. But how can I do this look up? It is a operational file which changes every day so how can I find the parent dynamic so I don't need to manually add 500 lines every day?

Regards, Paul

Re: Hierarchy

ogster1974 — Mon, 25 Jan 2016 14:32:52 GMT

if you can automate this process and its just the volume that's the hassle perhaps use a for loop to look through your data during load and build your hierarchy that way.

Re: Hierarchy

pauldamen — Mon, 25 Jan 2016 14:51:27 GMT

Could you guide me how that more or less would look?

Re: Hierarchy

ogster1974 — Mon, 25 Jan 2016 16:09:38 GMT

Hi Rahul

Try something like this:

Name:

LOAD * INLINE [

Name

G1000.F01

G1000.F03

G1000.G02

G1000.G03

G1000.G04

G1000.G05

G1000.F01

];

Number:

LOAD * INLINE [

Number

1.1

1.1.1

1.1.2

1.1.3

1.1.4

1.1

];

Let noNumberRows = NoOfRows('Number') - 1;

Let noNameRows = NoOfRows('Name') - 1;

For i = 0 To $(noNameRows)

LET vName = Peek('Name', $(i), 'Name');

For j = 0 To $(noNumberRows)

LET vNumber = Peek('Number', $(j), 'Number');

.....Load into new NameNumber table

NameNumber:

LOAD

* INLINE [

Name, Number

$(vName),$(vNumber)

];

Next j

Next i