In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Unique Tables into Columnar Data Range

BeerGuy
5 - Atom

I am trying to convert a data file comprised of many small tables into columns of data for easy use. I am still relatively new to Alteryx, and this is a bit out of my comfort zone, as it is not too simple. Tables such as the ones below go down ~20K rows:

BeerGuy_1-1686673201955.png

 

I need to convert to a data table like this, where each individual rate is a unique row, characterized by other the data entries:

YearMonthTermTypePeriodicityRate
2023JanuaryShort-TermAFRAnnual4.50%
2023JanuaryShort-TermAFRSemiannual4.45%
2023JanuaryShort-TermAFRQuarterly4.43%
2023JanuaryShort-TermAFRMonthly4.41%
2023FebruaryMid-TermAdjusted AFRAnnual2.89%
2023FebruaryMid-TermAdjusted AFRSemiannual2.86%
2023FebruaryMid-TermAdjusted AFRQuarterly2.85%
2023FebruaryMid-TermAdjusted AFRMonthly2.84%
2023MarchLong-TermAdjusted AFRAnnual2.84%
2023MarchLong-TermAdjusted AFRSemiannual2.82%
2023MarchLong-TermAdjusted AFRQuarterly2.81%
2023MarchLong-TermAdjusted AFRMonthly2.80%

 

I know there is an element of splitting the merged/title rows into columns based on a ">" delimiter, and then transposing different rows into columns. But, hoping someone with more experience will know how to do all of these in the appropriate order, I have really struggled with this one.

 

Thanks so much!

5 REPLIES 5
binuacs
21 - Polaris

@BeerGuy would you be able to provide a sample input file?

BeerGuy
5 - Atom

Sure - here is attached data, edited from original form as the full data set is about 10X as many tables. Thanks in advance for taking a look!

mceleavey
17 - Castor
17 - Castor

Hi @BeerGuy , love the name.

 

This is fairly simple if fiddly.

 

I first created a table identifier using a simple multi-row formula:

mceleavey_0-1686682062108.png

 

I then split the stream into header and detail lines using a filter.

The header line I simply split using text to columns and ended up with the following:

mceleavey_1-1686682125058.png

 

For the detail lines the original format is kind of header row header row, so I created a 1,2 identifier and pivoted the data around the table and row ID.

Now I simply created the four identifiers for each of the four columns of each "row", then used a filter to seperate them and join them back together so they were all on the same row. I ended up with:

mceleavey_2-1686682247319.png

 

...which I simply joined to the header stream on the table number:

mceleavey_4-1686682377064.png

 

mceleavey_0-1686682714720.png

 

 

Workflow attached.

I hope this helps.

 

M.

 

 



Bulien

BeerGuy
5 - Atom

@mceleavey thank you so much!!

 

This is a massive help, really appreciate it! Solved!

 

 

mceleavey
17 - Castor
17 - Castor

no problem.gif



Bulien

Labels
Top Solution Authors