Hello,
I have a data set that looks like:
Example:
Unique ID Value Year PD_in_1_year_ PD_in_2_years_ PD_3_years_from_now..........................................PD_in_30_years_from_now
A 1 2018 0,5 0,3 0,1
A 1.1 2019 0.5 0,3 0,1
A 1 2020 0,5 0,3 0,1
B 1.3 2018 0,33 0,22 0,6
B 2 2019 0,33 0,22 0,6
B 3 2020 0,33 0,22 0,6
B 1 2021 0,33 0,22 0,6
C 2 2018 0,7 0,1 0,4
C 4 2019 0,7 0,1 0,4
I am trying to make a workflow that will go through and:
Step 1:
Unique ID =A => take value (row 1, column 2 = 1 ) and multiply it with PD_in_1_ year (0,5). I would need output like Year 2018, 0,5
Step 2
Unique ID =A=> take value (row 2, column 2 = 1,1 ) and multiply it with PD_in 2_years (0,3). I would need output like Year 2019, 0,33
Step 3
Unique ID =A=> take value (row 3, column 2 = 1 ) and multiply it with PD_in 3_years .
Step 4
Unique ID =B => take value (row 4, column 2 =1,3) and multiply it with PD_in _1_year (0,33). Output like Year 2018, 0,429
Step 5
Unique ID =B => take value (row 5, column 2 =2) and multiply it with PD_in _2_year (0,22). Output like Year 2019, 0,44
Step 6
Unique ID =B => take value (row 6, column 2 =3) and multiply it with PD_in _3_year
Step 7
Unique ID= B => take value (row 7, column 2 =1) and multiply it with PD_in _4_year
Step 8
Unique ID =C => take value (row 8, column 2 =2) and multiply it with PD_in _1_year = 0,7
Step 9 ID=C => tale value from (row 9, column 2=4) and multiply it with PD_in _2_year = 0,1.
I tried several options, without any success. Any ideas?
Thanks!
Solved! Go to Solution.
There's a few transformations and a filter that make the final calculation possible here.
- Use a Tile tool to assign the record ID that's unique to each "Unique ID" value. This order will be used to determine what "PD_year" field to use.
- Transpose the "PD_year" fields and convert their names to just the year-difference number.
- Filter to only records where the tile number is the same as the year difference number.
- Perform the calculation.
Check out the example solution attached and let me know if that works for you.
Thanks a lot!
I spend a lot of time trying to solve this one. Tile was the tool I missed out.
But now it works flawlessly.
Cheers!