Alteryx Designer Desktop Discussions

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

Selecting the columns coming after a specific value in a cell

Gandalf_NotGrey
8 - Asteroid

Hi

I have a table where I have to select the columns coming after the text "Set B". This can be present in any column, and either in row 4, 5 or 6.

 

See image 2. In field F17, row 5, we have "Set B" written, my requirement is to select all the columns starting from F17, which is where "Set B" is written. F17 to F33.

Similarly, in image 3, the text "Set B" is in field F8 and in 4th row, so all the columns on and after F8 are needed. F8 to F33.

 

Is there any way to do this dynamically?? I have attached a sample file as well.

 

Also, sometimes the data, which is there in the excel file, comes in e notation when pulled into Alteryx, I don't want that to happen and get the data only in decimals without the e notation.

 

 

 

14 REPLIES 14
binuacs
21 - Polaris

@Gandalf_NotGrey If your column SET B is always in the row 4 in any of your input file then the attached workflow should work

image.png

goatley1
8 - Asteroid

I'm assuming you won't have to clean up the data as much as I did so the first filter will have to be customized. Essentially you need to ensure that Set B is the first record. Sample the first record. Then we transpose the fields and test for the Set B in the value field.  Use a multi row formula to find Set B and flag every record from set B while the field name is <= F33 using if then (I use 1 and 0).  Drop all the records that don't pass the multi-row formula with a 1.  Cross tab your field names back to columns.  Then drop all records with the sampling tool.  Union with the original filtered data set and keep only common fields.  This should work for your situations.  

 

Now I followed your instructions to a T this will always return the first field Fn (where n = any number form 1-33) through F33.  If you meant to grab the next n fields, the you will have to tweak the multi-row formula to accumulate instead of flag.  Subsequently filter for binary_test <= n.  Hope this Helps

goatley1
8 - Asteroid

This will only work for record 3 thus not dynamic.

 

Gandalf_NotGrey
8 - Asteroid

Thank You so much @binuacs for the response.

It would be very helpful if you can explain the purpose of the Dynamic Select Macro, so that I can troubleshoot the error I am getting.

I am getting the below error (image attached), while trying to replicate the solution you have suggested. Please see the attached image.

Tool 2 is the Dynamic Select Tool and for some reason it is saying that Output 3 is not invalid. 

 

Thanks

Gandalf_NotGrey
8 - Asteroid

And yes @goatley1 I am okay with that value in record 3 since that part I am able to make constant.

goatley1
8 - Asteroid

I'm sorry, I thought I had uploaded a solution I drafted. I'm not sure what that specific error is without more detailed context.  This workflow should get the job done for any input.  You may need to adjust the filter, because I'm assuming the data you have comes out slightly different than what is provided.  In my workflow you simply filter to make the record with Set_B the first record. everything else should work like clockwork.  No need for pesky macros.

binuacs
21 - Polaris

@Gandalf_NotGrey make sure that the summarize tool produces the correct formula similar to the given workflow

 

it should be like [name] = 'Field Name' OR [name] = 'Field Name', etc, if you are still getting the error, post the screenshot of your summarise tool output

flying008
15 - Aurora

Hi, @Gandalf_NotGrey  & @binuacs 

 

There is not need macro for this case. (  'Set B' always in the record 3 as you assign.)

 

录制_2025_04_26_10_03_47_777.gif

Gandalf_NotGrey
8 - Asteroid

Hello @flying008 Can you please attach your workflow.

Labels
Top Solution Authors