community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More
SOLVED

Tool Mastery | Dynamic Select

Highlighted
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?

 

Thanks!

ACE Emeritus
ACE Emeritus

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

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

Asteroid

Simple.....genius.......works great!

 

Thanks!

Labels