Hierarchy Drop Fields (of the NodeNames)

[#1]

Hi,

can somebody of you can tell me, how can I easily drop all NodeName-Fields from a hierarchy?

But I only Need the Node_Pathsource.

I wanted to drop the fields with

"Drop Fields like NodeName*" but it does not work

(I Need this that variable, because the Hierarchy is supposed to grow. At the Moment I have NodeName1 to NodeName13 but it can easily come to Field NodeName20...)

Thanks for your help in advance.

Chris

[hic · Accepted solution]

If your table name is 'Hierarchy', you could do a

For vFieldNumber = NoOfFields ('Hierarchy') to 1 step -1
  Let vFieldName = FieldName (vFieldNumber ,'Hierarchy');
  If WildMatch(vFieldName, 'NodeName*') Then
    Drop Fields $(vFieldName);
   End If
Next vFieldNumber

HIC

[#3]

If you have your level of hierarchy in a single table you can do like above suggested method .

Otherwise

Can you elaborate your scenario.

[#4]

Hi Henrik,

this solution is good,. Thank you very much.

Unfortunately the field "NodeName" is deleted as well.

I forgot that above, the field NodeName without the number is needed. Sorry for any inconvenience.

Is it complicated the Keep the "NodeName" without the number?

Chris

[hic]

OK, change your condition to e.g.

If WildMatch(vFieldName, 'NodeName*') and Len(vFieldName)>8 Then

HIC

[#6]

Hi Yeshu,

Henrik could help me a lot. This is great. Maybe you can help me with a last issue 🙂

I want to merge the two tables Tree and Treebridge.

Because I just need some Fields, that you can find in the attached qvw-file

Afterwards I can drop the Nodes_tmp-Table.

Thanks a lot in advance.

Chris

[hic]

I don't think that's a good idea...

• The Nodes_tmp table has exactly one record per node (691 records), and this is the proper place to keep all data pertaining to the nodes, including things like costs, account number, etc.
• The TreeBridge table has exactly one record per combination of node and ancestor (5698 records).
• The Tree table has exactly one record per node (691 records) but these have a different role than the Nodes in the first table. Each Tree record links, on the average, to 8 nodes.

If you start joining tables, you will get a data model that may show incorrect numbers and in addition uses more memory. So why should you do this? Further, if you drop the Nodes table, you will not be able to see relevant data, e.g. which assets you have within a sub-tree. I think that you right now have the optimal data model.

See also To Join or not to Join

HIC

[#8]

Hi Henric,

I understand your Point. Thanks for that.

But I have an issue regarding that.

Beginning from Line 190 in the Excel until 425 I have Profit-and-Loss-Values.

These should go into BS33020 (but WITHOUT the Hierarchy, just as a single line-value)

I wanted to get rid of Information (that are not neccessary for me) to include the sum of all P&L-Values as one line and not as Hierarchy.

Do you have an Idea how can I do that?

Chris

[hic]

I would load that Excel sheet in two rounds, with where clauses, so I get exactly the tables I want. E.g.

FirstTable:

Load... From ... Where Left(NodeID,2) = 'BS';

SecondTable:

Load... From ... Where Left(NodeID,2) = 'PL';

But I don't understand how you want to link the second part with the first.

HCI

[#10]

Hi Henric,

thanks for the Tip. The reason for the link is very easy. It is law (The Last Line of 'Profit and Loss' has to go the Balance Sheet).

The Question is: How do I get the sum of P&L into BS33020?

Chris