Script , cicle for to add rows linked to a specific field

[Bellamy]

Dear all,

I am sorry if the title is not easy to understand, I will try to explain it here.

I have a fact table in which there is a field that represent a product code that can be substituted from/to another product code.
Let's say this example:
|MAIN PRODUCT| |PRODUCT| |many other columns|
|Main product A| |Product 1| | .....|

Now, I have another QVD in which there is |PRODUCT| |SUBSTITUTE TO PRODUCT| |SUBSTITUTE FROM PRODUCT|
It means that for each "Product" (second column of fact) there could be 1 or more than 1 of substitution (from, and to)
For example Product 1 substitute from Product 0 and substitute to Product 2.

Now, in the new Fact table (let's say Fact2) I want to recreate all the combination as following:

|MAIN PRODUCT| |PRODUCT| |many other columns|
|Main product A| |Product 1| |......|
|Main product A| |Product 0| |....(same values obviously of the Product 1|
|Main product A| |Product 2| |....(same values obviously of the Product 1

Etc etc.

So, basically, I would like that the rows duplicate whenever and wherever there are sub to , and sub from, of a Product.
If a Product have 1 sub from, 1 sub to, and for example both of these have consequently other 1 sub from and 1 sub to (example Product -1 --> Product 0  --> Product 1 (the one picked from fact tabe) --> Product 2 --> Product 3) I would like to see 5 rows (1 already existing + 4 identic rows with the only difference of "Product").

I hope it could be clear, I tried to explain as much as detailed I can.
Thank you

[QFabian]

Hi @Bellamy , if i understood, maybe you can join your two tables. Here an example :

Fact:
Load * INLINE [
MAIN PRODUCT, PRODUCT, F3, F4
Main product A, Product 1,,
Main product A, Product 2,,
Main product A, Product 3,,
Main product A, Product 4,,
Main product B, Product 1,,
Main product B, Product 2,,
Main product B, Product 3,,
];

join
Subst:
Load * INLINE [
PRODUCT, SUBSTITUTE TO PRODUCT, SUBSTITUTE FROM PRODUCT
Product 1, Product 0
Product 1, Product 2
Product 1, Product 3
Product 2, Product 7
Product 3, Product 8
Product 4, Product 9
];

Table Chart output:

[Bellamy]

Hello QFabian, almost.. But thanks for the try.

What I would like to have in the output is only the column "Product" (and not the colum substitute from/to).
The column Product has to contains, in addiction of the Product already present in the fact table, also the all sub from and sub to, consequently row by row .

Today, as a "temporary" workaraound (or let me call it "as a dirty solution") I tried to join as many times I decided (a bit hard coded because I decided  to join 5 times for the sub from and 5 times for the sub to) in this way:

First time to the fact table (with Product A) I join left the product table to find the Sub from (that I will call "Sub from 1") and Sub to (that I will call "Sub from 1").
Then, I will again join the product table but this time as a key I will use the previous product "Sub from 1" and I will grab the one that I will call "Sub from 2" ....etc etc until "Sub from 5". Same workaround , with join left, for the "Sub to n" until 5.

--> as a result I will have same fact table as before but for every row I will have five new columns Sub from and five colums Sub To (total 10 new columns).
Then I just have to apply a "cross table" function because I want only ONE column "Product" that contains all the new 10 columns including the original one (11 potential row instead of 1 at the starting).

Main Product          | Product                                                                         | Ohers
Main Product A      | Prod 0 (it was real product from fact)              |
Main Product A      | Prod 1 (it was the first sub from the prod 0)  |
Main Product A      | Prod 2 (it was the sec sub from the prod 1     |
Main Product A      | Prod 3 (it was the first sub to the prod 0          |
etc

Hope it is more clear now. 
I think it's working fine BUT
1) it is hard coded (I decided to do 5 left join for SUB FROM and 5 left join for SUB TO and if there are more options I will miss them now, or if there are fewer I am overloading for nothing)
2) maybe it exists a more clean way to do it

Thanks

[QFabian]

@Bellamy  what about this example?

Fact:
Load * INLINE [
MAIN PRODUCT, PRODUCT, F3, F4
Main product A, Product 1,,
Main product A, Product 2,,
Main product A, Product 3,,
Main product A, Product 4,,
Main product B, Product 5,,
Main product B, Product 6,,
Main product B, Product 7,,
];

Subst:
Load * INLINE [
PRODUCT, SUBSTITUTE TO PRODUCT, SUBSTITUTE FROM PRODUCT
Product 1, Product 0
Product 1, Product 2
Product 1, Product 3
Product 2, Product 7
Product 3, Product 8
Product 4, Product 9
];

Map_Main:
mapping
Load
PRODUCT,
[MAIN PRODUCT]
Resident Fact;

concatenate(Fact)
Load
[SUBSTITUTE TO PRODUCT] as [PRODUCT],
applymap('Map_Main',PRODUCT) as [MAIN PRODUCT]
Resident Subst
Where not exists([MAIN PRODUCT], [SUBSTITUTE TO PRODUCT]);
drop table Subst;

table output: