recursive calculation on hierarchical probabilities in script

[yjin]

Let's say I have two tables:

data_assembly:

load * inline[

PartID,AssembledPartID

1, 20

3, 20

2, 21

20, 21

];

data_parts:

load * inline[

PartID, TestPassProb

1, 0.9999

2, 0.9998

3, 0.8

20,

21,

];

How do I implement a script in data load editor so that it can automatically computes the following table for data analytics:

data_parts_full_prob:

load * inline[

PartID, TestPassProb

1, 0.9999

2, 0.9998

3, 0.8

20, 0.79992

21, 0.79976

];

where TestPassProb  of PartID #20 is computed as TestPassProb of PartID #1 * TestPassProb of PartID #3.  I have already read this post and found it helpful.  What I need is a script function similar to lookup() but returns a list of matches instead of just the first one.

Thanks.

[Saravanan_Desingh · Accepted solution]

Try this,

data_assembly:
load * inline[
PartID,AssembledPartID
1, 20
3, 20
2, 21
20, 21
];

Join(data_assembly)
data_parts:
load * inline[
PartID, TestPassProb
1, 0.9999
2, 0.9998
3, 0.8
20,
21,
];

tab1:
HierarchyBelongsTo (PartID, AssembledPartID, NodeName, AssembledPartID, AssembledPartName, DepthDiff) 
LOAD *, PartID As NodeName
Resident data_assembly;

Drop Table data_assembly;

Left Join(tab1)

LOAD AssembledPartID, Evaluate(Concat(If(Len(Trim(TestPassProb))>0,TestPassProb),'*')) As Result
Resident tab1
Group By AssembledPartID;

[Saravanan_Desingh · Accepted solution]

Output:

[yjin]

That works, thank you!