Insert data into specific format when the headers don't match

[TKrueger]

I have designed an Alteryx flow to calculate a few values using various fields from an input. These values are calculated by date so I have 3 different values for one flow that needs to be in a certain format and 1 value for a different flow that needs to be in a specific format (this is attached). If I figure this one out, I think I can use the same logic for the other flow. 

I need to take the 6 year rate value from the Data tab and place it into the corresponding cell on the Final Format tab. The key is the Final Format has to stay in that exact format for our actuary models. I am not sure how to pull the 20220502 value from the Data tab and put it into cell G:6 (the corresponding 5/2/2022 cell) on the Final Format tab. 

Is there are way I can transpose the data on the Final Format tab or create a new field to pull the data in for each date?

I also thought I could recreate this Final Format tab somehow but I was also thinking using the shell and then inserting values would be easier.

Any help would be appreciated. Thanks!

[gabrielvilella]

You only have 2022 on your sample data. Do you have more than that on your actual dataset? 

[gabrielvilella]

Here is an example of what you can do. This works if you have only one year on the dataset. 

[TKrueger]

I will have more than one year going forward. This started in May, 2022 and will continue going forward. This table will have to be populated at least quarterly (only used quarterly but I may run the flow monthly in case something goes wrong). If it is easier to only have one year of data, I can also create a 2022 tab, 2023 tab, etc and use that tab when creating this final layout. 

[Felipe_Ribeir0]

Hi @TKrueger 

One way of doing this dynamically

[TKrueger]

Hi @Felipe_Ribeir0 

This worked very well, thank you for providing me with this workflow. I have a couple follow-up questions on items I am not sure on now:

1. Rather than replacing null values with 0, is there an easy way to use the value from the previous day? For example if there is no 20220514 or 20220515 values (because these are weekend days), I need to pull in the 20220513 value. I tried to do this in the multi field formula tool but I am doing something wrong. 

2. Is there a way to dynamically change the header row to start with Value1 (rather than Value) then continue to count Value2, Value3, etc then Value10, Value11, etc? Right now it starts at Value then Value2, Value3.....Value9, Value9_2, Value9_3, etc. 

Thanks again for the help on this!

[Felipe_Ribeir0 · Accepted solution]

Hi @TKrueger 

Please try the attached wf

[TKrueger]

Thank you @Felipe_Ribeir0. That worked well and I have accepted that as a solution. I appreciate the help! 

I have another similar output I have to generate and have a couple questions. This output uses a 1 year, 2 year and 6 year rate so I need to pull all 3 of those in for the 1st, 8th, 15th, and 22nd of each month. I have the rates calculated and you can see these on the Data tab of the attached file. I am struggling to get this into the desired output because I need 3 values for each date (20220501, 20220501, 20220501, 20220508, 20220508, 20220508, etc.) I apologize but the crosstab tool is confusing to me and I can't get it to keep all 3 values for each date. Once I get that done, I need to update the header row to be value1, value2.....value660. Alteryx wants to name them value1, value 2.......value9, value9_2, value9_3, etc. 

If you have any suggestions on how to create this flow, I would appreciate some guidance. If you are able to provide me with a workflow here, I will study both flows and make notes on the crosstab and multi field formula tools specifically. 

Thank you again! 

[Felipe_Ribeir0 · Accepted solution]

Hi @TKrueger 

Here it is:

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[Felipe_Ribeir0]

Hi @TKrueger 

It worked? If yes, please accept it as a solution. Thanks!