How to multiply a specific field for each level in hierarchy data

[agigliotti]

Hi,

I'm using HierarchyBelongsTo prefix to load a master-detail table.

Attached the result of HierarchyBelongsTo.

I'd need to multiply for each level the field "Factor".

The expected Factor value for the NodeID = PSF LOW MELT 2  should be calculated as below:

0,3333 (S PALLONE 290 P3)  * 1 (R BEST290ACC MF 01)  * 1 (FR MF 260 PACQ 01) * 1 (FE 260 ACCOP) * 0,9 (FE 235 BASE ACCOPP) * 0,4277 (5050VISPESBICD10) * 0,09 (PSF LOW MELT 2) = 0,01154674521

How can I achieve it?

Could someone help me?

It'd be very appreciated.

Many thanks in advance for your time.

Best Regards

@vinieme12 @hic @Chirantha @MarcoWedel @Oleg_Troyansky @sunny_talwar @marcus_sommer @MayilVahanan @Benoit_C 

[Kushal_Chawda · Accepted solution]

@agigliotti  one solution could be use hierarchy function after loading  data using HierarchyBelongsTo. You can create Key using NodeID & ParentID in HierarchyBelongsTo load to link with another load using hierarchy function. 

Data:
Hierarchy (NodeID, ParentID, Factor)
LOAD 
    NodeID,
    ParentID,
    NodeID&ParentID as Key,
    AncestorID,
    DepthDiff,
    if(len(trim(Factor))=0,0,Factor) as Factor
FROM HierarchyBelongsTo_Data; 

Factor_Calculation:
Load  Concat(Field,'*') as Factor_Calculation;
Load 'alt(Factor'& IterNo() &', 1)'  as Field
while IterNo()<=max_depth+1;
Load max(FieldValue('DepthDiff',RecNo())) as max_depth
AutoGenerate FieldValueCount('DepthDiff');

let vFactorCalculation = Peek('Factor_Calculation');

Drop Table Factor_Calculation;

Final:
Load Key,
    $(vFactorCalculation) as Factor_Required_Each_Level
Resident Data;

Drop Table Data;

[agigliotti]

Hi @Kushal_Chawda ,

Thanks for your help, but unfortunately the above Hierarchy (NodeID, ParentID, Factor)  never ends.

Take a look at the image below:

The HierarchyBelongsTo table has 38.453 rows.

What could be the reason?

Please let me know.

Best Regards

[Kushal_Chawda]

@agigliotti  can you share script? It should not happen

[agigliotti]

Hi @Kushal_Chawda ,

Thank you very much for your solution.
The problem was due to the Key field composition, I had to add some other fields.

Best Regards