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

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

agigliotti — Thu, 29 Jun 2023 21:12:07 GMT

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

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

Kushal_Chawda — Thu, 29 Jun 2023 23:45:39 GMT

@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;

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

agigliotti — Fri, 30 Jun 2023 08:40:41 GMT

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

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

Kushal_Chawda — Fri, 30 Jun 2023 11:10:35 GMT

@agigliotti can you share script? It should not happen

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

agigliotti — Sat, 01 Jul 2023 13:04:45 GMT

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