This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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.