I have some data where I Transpose the data to change some of the field names from an integer to a date using Find/Replace. I can't seem to figure out how to get the data back to the desired view like below.
Desired:
RecordID | column_1 | column_2 | column_3 | column_4 | 8/11/2021 | 8/12/2021 | 8/13/2021 | 8/14/2021 | 8/15/2021 | 8/16/2021 | 8/17/2021 | 7Day_total | 7Day_avg | 8/18/2021 | % Diff | alert |
1 | Data1 | owner1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 200 | alert | ||
2 | Data2 | owner1 | 3 | 4 | 4 | 3 | 3 | 0 | 0 | 17 | 2 | 0 | 200 | alert |
Source:
Name | Value |
RecordID | 1 |
column_1 | Data1 |
column_2 | |
column_3 | |
column_4 | owner1 |
8/11/2021 | 0 |
8/12/2021 | 0 |
8/13/2021 | 0 |
8/14/2021 | 0 |
8/15/2021 | 0 |
8/16/2021 | 0 |
8/17/2021 | 0 |
7Day_total | 0 |
7Day_avg | 0 |
8/18/2021 | 2 |
% Diff | 200 |
alert | alert |
RecordID | 2 |
column_1 | Data2 |
column_2 | |
column_3 | |
column_4 | owner1 |
8/11/2021 | 3 |
8/12/2021 | 4 |
8/13/2021 | 4 |
8/14/2021 | 3 |
8/15/2021 | 3 |
8/16/2021 | 0 |
8/17/2021 | 0 |
7Day_total | 17 |
7Day_avg | 2 |
8/18/2021 | 0 |
% Diff | 200 |
alert | alert |
Solved! Go to Solution.
Hi @BonusCup
Here is how you can do it.
Workflow:
1. Using first mult-row formula to calculate row id.
2. Using first mult-row formula to calculate col id. This will be used for column rename.
3. Using to summarize groupby on col id to get list of column names.
4. Using crosstab to convert to table with col name as col id.
5. Using dynamic rename to rename col id with col name.
Hope this helps : )
@atcodedog05 thanks for such a quick response and the solution to my issue. Reviewing your sample WF I can understand how you were able to solve it.
Thanks again
Happy to help : ) @BonusCup
Cheers and have a nice day!