Mapping Data To PDF Blocks

[Brandonzayid]

Hi All, 

So I have a table with data presented below:

I am trying to get these columns to be mapped to their respective pdf block accounts which are as shown below: 

Now the output has to be a format that is similar to the below presentation so I am able to just take upload them all for the system to read:

The other thing I am wondering is how I am able to automatically update the accounts so the earliest year is labeled with a 1 at the end of all of its accounts and the second earliest is labeled 2 and so on and so forth. I really would appreciate any help.

Thanks! 

[ggruccio]

Hi @Brandonzayid ,

Would something like the attached file work?  Basically use Record ID to number your rows, then transpose and combine field names with Record ID. I attached some additional logic to get the sort order on the fields correct in the end result.

[Brandonzayid]

Hi @ggruccio, 

Thank you so much! This helped a ton! I have a couple questions for you though.

1. In the Text Input for the Field Order, what is the logic behind the order of those field orders and if I were to add more columns than the 5 provided that need to be mapped to account id's what would I have to do to add these? 

2. If I were to include more than 5 rows (so more than 5 years), theoretically should this workflow automatically update the field names with the proper # at the end?

3. How would I map this following case: If I had data in that table for year 2019, this would be considered "Current Year". I would want this mapped to a specific account. Lets say these account names were the same but instead of a number at the end I would want CY (ex. BEGYEARCY, AMNTCY, etc.). 

4. If I were to add more columns to the data table, would I just need to copy the same step taken in the first select tool and rename the columns with my desired field? 

[ggruccio · Accepted solution]

Hi @Brandonzayid, glad to have helped!

Good Questions!  See below:

1. I did this to fit the order you showed in your final output.  The dataset gets a bit scrambled in the transpose....and in the final output there is no logical sort order that fits....(i.e. alphabetical will put the output in a different order than you have it)....it's a way to manually sort the fields in a custom order, and yes would need to be done for any new field you add as long as you wanted to have a custom sort order in the end.  If you are OK with alphabetical (or some other sort) you can ignore this step.

2.  Yes, It will work automatically - I've added a new row just to show how it works...but the awesome thing about Alteryx is you can create workflows that adapt to changes in the data.

3. Basically create some logic that pulls the max of the Record ID, converts it to "CY" and append back to your data.  (see the attached).

4.  Yes this would work this way as well.  I've given you a demo in the new file.

The only caution I have is that I've built this off your relatively small dataset.  If you have many more years you may run into some additional logic you need to build in.  For instance, to append the RecordID to the data labels requires converting the numeric data to a string.  If you have more than 9 years of data and you get a RecordID 10, given that it is a string, it will sort the data alphabetically, in which case 1, 10, 2, 3, 4, 5 ,6, 7, 8, 9.   You would just need to keep a numeric version of Record ID to assist with sorting, finding the max etc.