Dynamic Select Columns

[stapuff]

Time for a lifeline on this... just can't figure it out.

I get a dataset that changes by 2 columns every week and max is 41 columns. I want to return just the first 5 columns and the 2 newly added columns.  The first 5 columns are in every dataset. The 6th column is one of the newly added columns and easily gotten with a dynamic select formula.  I can not figure out how to select the 2nd newly added column since it moves every week.

[MarqueeCrew · Accepted solution]

This is how I would go about it;

 use field info tool. Join on fieldname between the datasets. 

what is in the unjoined new data are the fields of interest. 

cheers,

mark

[stapuff]

@MarqueeCrew 

I appreciate the response back. I admit I don't understand what you are suggesting.

Been trying to crack this code now for about 10 hours. Realizing the formula section of the Dynamic Select is of no value. Something as simple as ((Max([FieldNumber]-5)/2)+1 would have solved my issue. Can't pass variables to it when a Summerize Count would have solved this too.  

Being an old Excel guy this would have been solved in about 8 seconds, but I am trying really hard not to revert backwards.

Puff

[DavidP]

Let me start by saying the this is @MarqueeCrew 's idea so please credit him with the solution - this is just my interpretation of it.

Once I have identified the list of fields I want to keep, I use a dynamic rename to write something to the description of those fields and then use dynamic select to remove all fields that don't have something in the description.

Workflow example attached.

[stapuff]

@MarqueeCrew @DavidP 

First off... both of you are treasures to this community. I have seen your names attached to 100's of posts and video's.

I was able to come up with a solution at the "literal" 11th hour.

The description of the original post should have been clearer and an assumption was forced that didn't exist and may have caused confusion.

There is a singular data set... nothing to test against. The problem with this project is once the data is processed (what this will achieve) it is then moved to a database and stored, consumes, etc and not accessible to test against.

So I ended up taking the old man approach.  Nothing sexy or shiny like rename or dynamic selects... just good old formula's and filters. I was using the field info tool, which appears to be a main staple in my Alteryx diet as of late right along with the multirow formula tool.

Disregard the container tools in the image below and this is how I solved it:

Puff

[DavidP]

Hi Puff,

Thank you for your kind words and well done on coming up with a solution. It is super satisfying when you can crack a problem that you've been battling with for hours.

The Transpose/Crosstab design pattern you're using here with the logic inbetween to remove the fields you don't want is a powerful tool in Alteryx.

It works fine in many cases, but has a few quirks, for instance, Crosstab changes all non-alphanumeric characters in your field names to underscores and it re-orders your columns alphabetically. There are some tricks around this, like adding a Tile Tool to your transposed data and then using the SequenceNumber field as the name field in your CrossTab and Grouping by the original Field name, then using Dynamic rename to restore your field names, but it's a bit messy.  For these reasons, I have come to prefer methods where I don't have to transpose and crosstab my data sets, if possible, as shown in the technique  I posted earlier.

Looking at your example data, it seems to me that the pattern is to keep the 1st 5 fields and then also keep the 1st Sales field and the 1st Units field. If this assumption holds true, it can be achieved like this:

Whatever the logic is to identify the 2 fields you want to keep with the original 5, you can build that out and then use the dynamic rename/dynamic select technique to retain those columns from your original data set.

Example workflow attached.

[stapuff]

Hey @DavidP ,

As always I appreciate your response.

I think I have mentioned before that I am an old Excel/VBA guy. Been doing it a looooooong time. Was handed Alteryx a year ago and putzed around with it for a better part of the year. Got hot and heavy a couple of months ago. More now since the world is pretty much in lockdown. I call it the "introverts paradise".

I have tried really hard not to get wrapped up in the macro's side of it because learning the fundamentals are far more important to me. So when I see solutions like what you posted that are clean, functional, and on point... I take notice, study, etc. BTW... all those quirks you mentioned... LOL .... ya I noticed them and your assumption was exactly what I was looking for.

I am in that awkward frustrating Alteryx puberty stage... you know... waiting for my mustache to start growing in. Left with several choices.... just have Excel do the job (could have been done on a country minute), have Alteryx call a macro I already made, or spend 12 hours working of 15 different versions of what I posted.  I choose the latter.

Going to give your example a whirl tomorrow morning or in about 3 hours, whichever comes first... LOL

Puff 

[DavidP]

Hi Puff,

I get where you're coming from! None of us ever stop learning. It's a short hop from where you are now to the place where you can do things 1000 times faster in Alteryx than you ever could in Excel. All the logic and skills that you learnt writing VBA will stand you in good stead in Alteryx too and if you really miss coding you can try out the Python tools in Alteryx! If I can offer 3 bits of advice to accelerate your learning

1. Keep posting - there's no point ever getting stuck, the guys here on the Community will come up with 5 different approaches to solving your issue in less time than it takes you to write up the post

2. Go through the learning material in the Academy section, the interactive lessons are great and you can pick and choose which to do, they're short and sharp

3. Weekly challenges - that's how your learning really accelerates! There's over 200 problems to work through. Don't just look at the official solution, also look at how others solved the problem - it's great for picking up ideas. 

Happy Alteryxing!