Hierarchy

[pauldamen]

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

[ogster1974]

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.

[pauldamen]

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

[ogster1974]

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.

[pauldamen]

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

[ogster1974]

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.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