Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Tool Mastery | Dynamic Select

Bonediggler
9 - Comet

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!

3 REPLIES 3
bsharbo
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 :-))

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

Bonediggler
9 - Comet

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

 

Thanks!

Labels