Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
Could you guide me how that more or less would look?
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