Deselecting columns due to a separate list of columns to remove

[paddykav]

Hi all,

Hopefully someone can help.

I have a very large data set but need to deselect columns based on another data input.

I need a way to automate this in the workflow without manually using the select tool since the similar thing needs to be done on many datasets. Please let me know if this can be done?

Row	A	B	C	D	E	F
1	12	Duck	DD	jlkj	67ty	00
2	13	Cow	DD	lkjlk	78gh
3	12	Fox	DD	kjlk	89hg
4	13	Hen	AA	kjlkj	56f
5	14	Pig	AA	kjk	45df
6	15	Dog	AA	lkjlk	5fg	99
7	12	Cat	FF	kljl	7vb	100

Separate data source that commonly changes:         Columns to exclude from report if present

B

E

X

Y

Z

[AngelosPachis]

Hi @paddykav ,

I can think of two ways you could do that. The first would be with a dynamic select tool, where you type the columns you don't need in an in function.

 so

[Name] NOT IN ("B","E","X","Y","Z")

The other way would be to transpose your dataset and then  join it on your data input tool, but this is probably not the way to go if you have a very large dataset

Hope one of the two ways work for you.

Cheers,

Angelos

[paddykav]

Thank you but typing it in isn't an option here since I need to do this many times with different data sets and the columns to exclude will change based on the secondary input of columns to exclude.

And yes, since the data set has many millions of rows it's not suitable to transpose 

[AngelosPachis]

Hi @paddykav ,

Another alterative would be to use a batch macro, where you feed in the control parameter of the batch macro the fields you want to exclude and that dynamically updates the values in the In function of the dynamic select tool.

So your input is 

and the fields you want to exclude is

You have to bring those fields in the correct format to be fed inside the In function. You can use a summarize tool to do that.

That's fed in the control parameter and inside your batch macro, you have a single dynamic select tool where you dynamically update the column values to be excluded

In your output, you should get the desired outcome

Hope that helps,

Angelos

[AngelosPachis]

@paddykav tested it as well in a different dataset

[paddykav]

Thank you very much👍. This looks very promising 

I tried to download the flow but it won't open for me since it says it's build using a more recent version and I'm stuck with v2020.3 for now and can't upgrade since that's what my server is at. I'll try tomorrow to download a second version of Alteryx to my PC with the latest. You must have built it on 2021.1?

[AngelosPachis]

Yep, but I have also attached version 2020.3 in this comment since this is your server version

Let me know if that worked for you.

Regards,

Angelos

[paddykav]

Thank you very much. I was able to open this file and work with it.

This solution is definitely getting me to where I want to go