I've a list of older vehicles and their respective parts. I want to analyze which parts still have demand, forecast, inventory, suppliers to produce the parts, etc. etc. This isn't so hard except parts can get substituted to another part number when the part has engineering revisions, becomes part of a kit, etc., etc. and this can go many levels deep.
Our Part Substitution table is is in the format of "Sub From PN" and "Sub To PN" like this ...
| Sub From PN | Sub To PN |
| A | B |
| B | C |
| C | D |
| D | E |
I was able to join this data against itself over and over again in Cognos and expand the data to be on a single row with each successive level of substituted part in the next column; however, I can't join a "Part Attributes" table in Qlik Sense to 10+ fields in a single Part Substitution table in order to apply filters and analyze the vehicles/parts. Instead, I believe I need to keep the data in 2 columns like this ...
| Original PN | Sub To PN |
| A | A |
| A | B |
| A | C |
| A | D |
| A | E |
It seems this format would allow me to display the vehicle, the original part, any substituted parts, and join a Part Attributes table where I can filter and perform analysis.
I'm not sure if there's a name for this kind of expansion of lineage and I cannot seem to find a sample of such operation. Have you seen any sample code like this which would loop or join the data against itself over and over again to produce this kind of output?
NOTE: The Sub From PN field doesn't contain distinct values. For example, there could be PN A subbing to PN B and the next row could also have PN A subbing to PN C. That said, I need to keep the association of A to B/C but also find out if B/C sub deeper and associate those parts back to A.
