Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Restructuring data

Nikkib
7 - Meteor

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 Date10-mrt-06 Exhibition End Date01-apr-06
       
       
DayVisitorsDiscounted tickets  DayVisitorsDiscounted Tickets
1,08018 1,018018
2,029318 2,018012
3,049718 3,039412
4,064518 4,051012
5,075018 5,05109
6,086918 6,06569
7,086918 7,06726
8,087212 8,07816
9,099612 9,09806
10,0120812 10,012096
    11,012566
    12,012586
    13,012766
    14,015536

 

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: 

 

NameDayVisitors Discounted tickets Operator Name 
Van Gogh museum1,08018PDO
Van Gogh museum2,029318PDO
Van Gogh museum3,049718PDO
Van Gogh museum4,064518PDO
Van Gogh museum5,075018PDO
Van Gogh museum6,086918PDO
Van Gogh museum7,086918PDO
Van Gogh museum8,087212PDO
Van Gogh museum9,099612PDO
Van Gogh museum10,0120812PDO
Rijksmuseum1,018018PDO
Rijksmuseum2,018012PDO
Rijksmuseum3,039412PDO
Rijksmuseum4,051012PDO
Rijksmuseum5,05109PDO
Rijksmuseum6,06569PDO
Rijksmuseum7,06726PDO
Rijksmuseum8,07816PDO
Rijksmuseum9,09806PDO
Rijksmuseum10,012096PDO
Rijksmuseum11,012566PDO
Rijksmuseum12,012586PDO
Rijksmuseum13,012766PDO
Rijksmuseum14,015536PDO

 

Is there any way I can get it in this format? 

 

Thanks in advance!

6 REPLIES 6
danilang
19 - Altair
19 - Altair

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

 

WF.png

 

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.

 

Results.png

 

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

Nikkib
7 - Meteor

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 

 

 

 

danilang
19 - Altair
19 - Altair

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 

Nikkib
7 - Meteor

Hi @danilang

 

Have added it in the attachments :) 

 

Thanks, 

 

Nikki 

danilang
19 - Altair
19 - Altair

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).

WFv2.png   

 

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

Nikkib
7 - Meteor

Thanks a lot for all your help Dan, works like a charm! 

Labels