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: I need to convert to a data table like this, where each individual rate is a unique row, characterized by other the data entries: YearMonthTermTypePeriodicityRate2023JanuaryShort-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!

Unique Tables into Columnar Data Range

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:

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

Year	Month	Term	Type	Periodicity	Rate
2023	January	Short-Term	AFR	Annual	4.50%
2023	January	Short-Term	AFR	Semiannual	4.45%
2023	January	Short-Term	AFR	Quarterly	4.43%
2023	January	Short-Term	AFR	Monthly	4.41%
2023	February	Mid-Term	Adjusted AFR	Annual	2.89%
2023	February	Mid-Term	Adjusted AFR	Semiannual	2.86%
2023	February	Mid-Term	Adjusted AFR	Quarterly	2.85%
2023	February	Mid-Term	Adjusted AFR	Monthly	2.84%
2023	March	Long-Term	Adjusted AFR	Annual	2.84%
2023	March	Long-Term	Adjusted AFR	Semiannual	2.82%
2023	March	Long-Term	Adjusted AFR	Quarterly	2.81%
2023	March	Long-Term	Adjusted AFR	Monthly	2.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!

Tips and Tricks

Custom Tools

Best Practices

Dynamic Processing

Accepted answers

mceleavey

Hi @BeerGuy , love the name.

This is fairly simple if fiddly.

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

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:

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:

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

Workflow attached.

I hope this helps.

BeerGuy_mceleavey.yxmd

BeerGuy

@mceleavey thank you so much!!

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

All comments

binu_acs

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

BeerGuy

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!

Rates.xlsx

mceleavey

Hi @BeerGuy , love the name.

This is fairly simple if fiddly.

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

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:

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.

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

Workflow attached.

I hope this helps.

BeerGuy_mceleavey.yxmd

BeerGuy

@mceleavey thank you so much!!

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

mceleavey

no problem.gif

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703