Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Tool Mastery | Dynamic Select

8 - Asteroid

Experts - 


Little issue here: I am extracting data from a warehouse and running it through a bunch of tools to get it shapely enough to fit into an oddly designed MS Access table.  Basically the table is a sort of giant cross tab report (don't ask me why) with - among other things - claim numbers going across the top.  For example: Name, Claim_1, Claim_2, Claim_3 up to Claim_15.



To get claim position by member I use a ranking formula, and in order to get the data ready for loading this table I use crosstab and summary tools.  The problem is there will not always be 15 claims for any one member (sometimes the max per member is only 2), in which case the summary tools will throw an error ("The field Claim_15 is not contained in the record").


Any ideas for a workaround?



11 - Bolide

Hello! Can you provide some example data that is fake, along with the workflow code that you are using (and your desired output if you could get your code to work).


I am not 100% sure what you are trying to accomplish so I think it will help others figure out and provide you better answers.


Example: I could be wrong, but i think it sounds like you should be pivoting all columns that have the words "Claim_" in them, that way you have one row per Name for each claim. 


Seeing where exactly things are going wrong for you will allow me to come up with a different solution (or the same one only more dynamic for you :-))

14 - Magnetar

If you can connect to the MS Access table with a second input data tool, you can do something like "SELECT TOP 0 * FROM [TABLE]" in the Access table, and then Union this to your cross-tabbed data.

This will ensure that all fields exist in your output, without creating any additional records.

8 - Asteroid great!