Hi all :)
I have a problem with some messy data.
I receive data from another system (strucuture is fixed unfortunately) which contains 3 columns containing different values.
Its basically many data tables added together one below the other. Which means one set of data looks like this (this is an extract)
| First field | Month | Value |
| Name1 | | |
| 2015 | January | 11,5096479 |
| | February | 23,8781814 |
| | March | 25,1236438 |
| | April | 67,0685224 |
| | May | 20,1968975 |
| | June | 60,984966 |
| | July | 65,1069862 |
| | August | 73,8685958 |
| | September | 58,219544 |
| | October | 86,7899767 |
| | November | 83,8234705 |
| | December | 16,772623 |
| 2016 | January | 67,8943966 |
| | February | 13,5733143 |
| | March | 72,7014173 |
| | April | 26,3477572 |
| | May | 65,4552399 |
| | June | 12,7835286 |
| | July | 5,99862806 |
| | August | 73,823003 |
| | September | 45,054345 |
| | October | 50,7705732 |
| | November | 24,273235 |
| | December | 16,0405277 |
The Name is the unique identifier of the data set. The set goes on until 2022 December.
What I try to achieve: Cross tab the data to have a list of names in the first column and all the months dates horizontally. In that case:
| ID | Jan 15 | Feb 15 | Mrz 15 | Apr 15 | Mai 15 | Jun 15 | Jul 15 | Aug 15 | Sep 15 | Okt 15 | Nov 15 | Dez 15 | Jan 16 | Feb 16 | … |
| Name1 | Values | Values | Values | Values | Values | Values | Values | Values | Values | Values | Values | Values | Values | Values | … |
This is easily manageable for me. The problem I face is that there are almost 1.000 of these data sets below each other. Which means I have to manually seperate those 1.000 sets first before cross tab them...
Does anyone have an idea how to achieve that? I attache a sample of the data conatining the Input and desired output tabel as example.
Thank you! :) very curious to see if anybody has an idea
