Hi All,
I have input data where the top 3 rows are headings of the table. I want to merge them into one cell so I can put them as headers. Can someone please help determine the best way to transform this data?
Below is my input data and expected output format for reference.
Input Data | |||||||||||||||||||
[Null] | [Null] | [Null] | Security | 1 Yr | 2 Yrs* | 3 Yrs* | 4 Yrs* | 5 Yrs* | 10 Yrs* | 1 Yr | 1 Yr | 1 Yr | 1 Yr | 1 Yr | 1 Yr | 1 Yr | 1 Yr | 1 Yr | 1 Yr |
[Null] | [Null] | [Null] | Category | To | To | To | To | To | To | To | To | To | To | To | To | To | To | To | To |
[Null] | [Null] | Currency | Description | 12/31/2023 | 12/31/2023 | 12/31/2023 | 12/31/2023 | 12/31/2023 | 12/31/2023 | 12/31/2014 | 12/31/2015 | 12/31/2016 | 12/31/2017 | 12/31/2018 | 12/31/2019 | 12/31/2020 | 12/31/2021 | 12/31/2022 | 12/31/2023 |
Expected Output | |||||||||||||||||||
[Null] | [Null] | Currency | Security Category Description | 1 Yr to 12/31/2023 | 2 Yrs* to 12/31/2023 | 3 Yrs* to 12/31/2023 | 4 Yrs* to 12/31/2023 | 5 Yrs* to 12/31/2023 | 10 Yrs* to 12/31/2023 | 1 Yr to 12/31/2014 | 1 Yr to 12/31/2015 | 1 Yr to 12/31/2016 | 1 Yr to 12/31/2017 | 1 Yr to 12/31/20188 | 1 Yr to 12/31/2019 | 1 Yr to 12/31/2020 | 1 Yr to 12/31/2021 | 1 Yr to 12/31/2022 | 1 Yr to 12/31/2023 |
Solved! Go to Solution.
I would start by transposing the data so the data you want to join is in a row. Use the record ID tool, then the summarise tool, grouping by record ID just generating and the concatenating the other fields, then crosstab tool for names form the newly concatenated field and values from record ID and then go from there let me know how you get on
Thanks for your response. I tried this but couldn't proceed after summaries tool. can you please share sample workflow so that i will follow the steps in real data.
I have done this but different way. thanks for your response.
I'm curious how you did this, if you could share your solution, I have exact same question, can't figure out simplest way to do it that will also be dynamic enough for any future input.
Hi, can you please share your solution, I have same exact problem but can't quite come up with the best dynamic approach.