Tool Mastery | Dynamic Select

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!

Transformation

Accepted answers

Claje

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.

All comments

bsharbo

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

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

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

Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703