Hi,
Today I came across the same problem as you have originally described, however mine was in relation to a customer hierarchy, not a product. In any case, the solution would be the same.
Basically what you need to do is rebuild the parent/child relationship so you can then load using the hierarchy load feature. To do this, I loop through all the possible levels of the hierarchy and create the parent/child relationship between each node. I use distinct to ensure I do not duplicate:
FOR iLevel = 1 TO 9;
LET iChildLevel = $(iLevel) + 1;
TEMP_HIER_TABLE:
LOAD DISTINCT
CUST_CODE_$(iLevel) as PARENT_CUST_CODE,
CUST_CODE_$(iChildLevel) as CHILD_CUST_CODE
RESIDENT STANDARD_HIER
WHERE Not IsNull(CUST_CODE_$(iChildLevel));
NEXT iLevel;
LEFT JOIN (TEMP_HIER_TABLE)
LOAD CUST_CODE as CHILD_CUST_CODE,
CUST_NAME as CHILD_CUST_NAME
RESIDENT CUSTOMER;
HIER_TABLE:
HIERARCHY (CHILD_CUST_CODE, PARENT_CUST_CODE, CHILD_CUST_NAME, PARENT_CUST_NAME, CHILD_CUST_NAME, HIER_PATH, '-')
LOAD CHILD_CUST_CODE,
PARENT_CUST_CODE,
CHILD_CUST_NAME,
CHILD_CUST_CODE AS CUST_CODE
RESIDENT TEMP_HIER_TABLE;
DROP TABLE TEMP_HIER_TABLE;
The STANDARD_HIER table just stores the levels of the hierarchy.
Hope this helps!
