Hi,
I have a set of data for different museums that is structured as follows:
Name: | Van Gogh museum | Name: | Rijksmuseum | |||
Operator Name: | PDO | Operator Name: | PDO | |||
Country Name: | NL | Country Name: | NL | |||
Units of Measurement: | Visitors | Units of Measurement: | Visitors | |||
Sold art: | Sold art: | |||||
Borrowed art: | 8 | Borrowed art: | 8 | |||
Exhibition End Date | 10-mrt-06 | Exhibition End Date | 01-apr-06 | |||
Day | Visitors | Discounted tickets | Day | Visitors | Discounted Tickets | |
1,0 | 80 | 18 | 1,0 | 180 | 18 | |
2,0 | 293 | 18 | 2,0 | 180 | 12 | |
3,0 | 497 | 18 | 3,0 | 394 | 12 | |
4,0 | 645 | 18 | 4,0 | 510 | 12 | |
5,0 | 750 | 18 | 5,0 | 510 | 9 | |
6,0 | 869 | 18 | 6,0 | 656 | 9 | |
7,0 | 869 | 18 | 7,0 | 672 | 6 | |
8,0 | 872 | 12 | 8,0 | 781 | 6 | |
9,0 | 996 | 12 | 9,0 | 980 | 6 | |
10,0 | 1208 | 12 | 10,0 | 1209 | 6 | |
11,0 | 1256 | 6 | ||||
12,0 | 1258 | 6 | ||||
13,0 | 1276 | 6 | ||||
14,0 | 1553 | 6 |
The list continues to horizontally and vertically, meaning that there will be more museums below van gogh museum and to the right of Rijksmuseum.
What I am looking for is the following:
Name | Day | Visitors | Discounted tickets | Operator Name |
Van Gogh museum | 1,0 | 80 | 18 | PDO |
Van Gogh museum | 2,0 | 293 | 18 | PDO |
Van Gogh museum | 3,0 | 497 | 18 | PDO |
Van Gogh museum | 4,0 | 645 | 18 | PDO |
Van Gogh museum | 5,0 | 750 | 18 | PDO |
Van Gogh museum | 6,0 | 869 | 18 | PDO |
Van Gogh museum | 7,0 | 869 | 18 | PDO |
Van Gogh museum | 8,0 | 872 | 12 | PDO |
Van Gogh museum | 9,0 | 996 | 12 | PDO |
Van Gogh museum | 10,0 | 1208 | 12 | PDO |
Rijksmuseum | 1,0 | 180 | 18 | PDO |
Rijksmuseum | 2,0 | 180 | 12 | PDO |
Rijksmuseum | 3,0 | 394 | 12 | PDO |
Rijksmuseum | 4,0 | 510 | 12 | PDO |
Rijksmuseum | 5,0 | 510 | 9 | PDO |
Rijksmuseum | 6,0 | 656 | 9 | PDO |
Rijksmuseum | 7,0 | 672 | 6 | PDO |
Rijksmuseum | 8,0 | 781 | 6 | PDO |
Rijksmuseum | 9,0 | 980 | 6 | PDO |
Rijksmuseum | 10,0 | 1209 | 6 | PDO |
Rijksmuseum | 11,0 | 1256 | 6 | PDO |
Rijksmuseum | 12,0 | 1258 | 6 | PDO |
Rijksmuseum | 13,0 | 1276 | 6 | PDO |
Rijksmuseum | 14,0 | 1553 | 6 | PDO |
Is there any way I can get it in this format?
Thanks in advance!
Solved! Go to Solution.
Hi @Nikkib
Any time you have a input with an extended header section followed by rows of data, you have to break it up and treat the header and data sections separately
The top branch of this workflow build the Museum name list. The second, the Operator Name List and the third transposes the attendance data, cleans the field names. All three branches get a museum number which is used in the Multi-join to align the records. The Crosstab moves the Day, Visitors and Discounted Tickets info into their own columns.
Note: I added a 3rd set of museum columns to show that the solution is scalable to any number of columns.
Also Note: your original data had the header and attendance info for Rijksmuseum offset by one column. I assumed, probably incorrectly, that this was an error when you created the sample data and that the original data is aligned. If it's not, post a sample here and I'll see what I can do.
Dan
Thanks @danilang!
Exactly what I was looking for and indeed error in copy pasting.
What I am now also facing is the following:
the museums are not only structured horizontally but also vertically. So meaning that below van Gogh museum there would be another museum. Do you have any ideas on how to tackle that? There would always be an empty row between the data in the first set of museums and the second set.
Nikki
Hi @Nikkib
We're starting to get a lot of possible combinations. Can you post a sample file may be 3 museums wide and 3 high. That'll ensure that I'm not making any data assumptions
Thanks
Dan
Hi @Nikkib
Since your new input file is essentially just a series of the original input file all stacked on top of each other, I was was able to reuse my original WF by changing it to an Iterative Macro(the Museum Icon below).
The WF uses a Dynamic Select to ignore all the blank columns,F4, F8, F12, etc. The Multi-Row tool creates groups of rows based on the repeated header information. Within the macro, the only change I had to make to the additional was a Filter tool to process each group in turn. The rows that drop out the false output of the Filter go back around to the I Macro input and are reprocessed. The macro stops when there are no more groups left to process.
Dan
Thanks a lot for all your help Dan, works like a charm!