Dynamic Select Column based on data in another column

[J-Riedel]

Hi,

I am looking for a way that I could use the Dynamic Select tool to choose columns based on the data that is in another field.

The data that I have shows all of the months of the year for both activity and YTD balance, along with some fixed fields (i.e. Account #, Company #).  I need to select specific fields to return and I am using the dynamic select tool to select these fixed fields based on their  FieldNumber as these will stay constant.

I have fields in my data for the current month and the previous month and I would like to setup the dynamic select to only select the fields that contain the data in the CurrentMon (for both activity and YTD Balance) and PrevMon (YTD Balance Only) fields. 

Is there a way to do this with the dynamic select? Or is there possibly a different way to do it?

Below is my starting data before using the dynamic select:

GL COMPANY NBR	GL COMPANY NAME	GL ACCOUNT NBR	GL ACCOUNT NAME	GL AU NBR	EFFECTIVE DATE	JAN ACTIVITY	JAN YTD BAL	FEB ACTIVITY	FEB YTD BAL	MAR ACTIVITY	MAR YTD BAL	APR ACTIVITY	APR YTD BAL	MAY ACTIVITY	MAY YTD BAL	JUN ACTIVITY	JUN YTD BAL	JUL ACTIVITY	JUL YTD BAL	AUG ACTIVITY	AUG YTD BAL	SEP ACTIVITY	SEP YTD BAL	OCT ACTIVITY	OCT YTD BAL	NOV ACTIVITY	NOV YTD BAL	DEC ACTIVITY	DEC YTD BAL	RECON AU	RECON AU MANAGER NAME	RECONCILER	OUTPUT CYCLE/YEAR	Period End Date	CurrentMon	PrevMon	CurrentYr
0	Company 1	1	Cap Stk	10	9/7/2021	-	100.00	-	100.00	-	100.00	-	100.00	-	100.00	-	100.00	-	100.00	-	100.00	-	100.00	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
0	Company 1	2	APIC	10	9/7/2021	-	1,500.00	-	1,500.00	-	1,500.00	-	1,500.00	-	1,500.00	-	1,500.00	-	1,500.00	-	1,500.00	-	1,500.00	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
0	Company 1	2	APIC	11	9/7/2021	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	52	Jim Doe1		F2021	8/31/2021	Aug	Jul	2021
0	Company 1	3	RE Other	12	9/7/2021	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	89	Steve Doe1		F2021	8/31/2021	Aug	Jul	2021
0	Company 1	4	Dividends Paid Other	10	9/7/2021	500.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
0	Company 1	5	RE	99	9/7/2021	(2,500.00)	5,000.00	-	5,000.00	-	5,000.00	-	5,000.00	-	5,000.00	2,500.00	7,500.00	-	7,500.00	-	7,500.00	-	7,500.00	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
0	Company 1	6	Dividends Paid	10	9/7/2021	1,000.00	-	-	-	(1,000.00)	(1,000.00)	-	(1,000.00)	-	(1,000.00)	(1,000.00)	(2,000.00)	-	(2,000.00)	-	(2,000.00)	-	(2,000.00)	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
6	Company 2	1	Cap Stk	20	9/7/2021	-	10.00	-	10.00	-	10.00	-	10.00	-	10.00	-	10.00	-	10.00	-	10.00	-	10.00	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
6	Company 2	2	APIC	20	9/7/2021	-	750.00	25.00	775.00	25.00	800.00	-	800.00	-	800.00	-	800.00	-	800.00	-	800.00	-	800.00	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
6	Company 2	5	RE	99	9/7/2021	-	1,000.00	-	1,000.00	-	1,000.00	-	1,000.00	-	1,000.00	-	1,000.00	-	1,000.00	-	1,000.00	-	1,000.00	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
6	Company 2	6	Dividends Paid	20	9/7/2021	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
9	Company 3	2	APIC	30	9/7/2021	-	2,000.00	-	2,000.00	-	2,000.00	-	2,000.00	-	2,000.00	-	2,000.00	-	2,000.00	-	2,000.00	-	2,000.00	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
9	Company 3	5	RE	99	9/7/2021	-	3,000.00	-	3,000.00	-	3,000.00	-	3,000.00	-	3,000.00	-	3,000.00	-	3,000.00	-	3,000.00	-	3,000.00	-	-	-	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021

Below is the data that I am trying to obtain after the dynamic select - since my Current Month is "Aug", I want to show Aug Activity and Aug YTD Bal; since my PrevMon is "Jul", I want to show Jul YTD Bal.

GL COMPANY NBR	GL COMPANY NAME	GL ACCOUNT NBR	GL ACCOUNT NAME	GL AU NBR	EFFECTIVE DATE	JUL YTD BAL	AUG ACTIVITY	AUG YTD BAL	RECON AU	RECON AU MANAGER NAME	RECONCILER	OUTPUT CYCLE/YEAR	Period End Date	CurrentMon	PrevMon	CurrentYr
0	Company 1	1	Cap Stk	10	9/7/2021	100.00	-	100.00	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
0	Company 1	2	APIC	10	9/7/2021	1,500.00	-	1,500.00	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
0	Company 1	2	APIC	11	9/7/2021	-	-	-	52	Jim Doe1		F2021	8/31/2021	Aug	Jul	2021
0	Company 1	3	RE Other	12	9/7/2021	-	-	-	89	Steve Doe1		F2021	8/31/2021	Aug	Jul	2021
0	Company 1	4	Dividends Paid Other	10	9/7/2021	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
0	Company 1	5	RE	99	9/7/2021	7,500.00	-	7,500.00	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
0	Company 1	6	Dividends Paid	10	9/7/2021	(2,000.00)	-	(2,000.00)	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
6	Company 2	1	Cap Stk	20	9/7/2021	10.00	-	10.00	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
6	Company 2	2	APIC	20	9/7/2021	800.00	-	800.00	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
6	Company 2	5	RE	99	9/7/2021	1,000.00	-	1,000.00	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
6	Company 2	6	Dividends Paid	20	9/7/2021	-	-	-	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
9	Company 3	2	APIC	30	9/7/2021	2,000.00	-	2,000.00	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021
9	Company 3	5	RE	99	9/7/2021	3,000.00	-	3,000.00	1	John Doe1	B3	F2021	8/31/2021	Aug	Jul	2021

I have attached an Excel files with the data examples.

Thank you!

[J-Riedel]

Attaching the Excel files with the data examples

[ConnorK]

Hi @J-Riedel ,

Please see the attached workflow. I used a transpose tool to flip the month columns and then dynamically filtered on the column name for the current month and month -1 through a filter. Once this is done you can flip the data back to its original state with a cross-tab tool.

I hope this helps!

[csh8428]

@J-Riedel 

I think this should work for ya. 

[J-Riedel]

@csh8428 

Thank you for the sample.  I am unable to open the sample file as I must have an older version of Alteryx.  Is there a way you could save differently, or how would I be able to open?

Thank you.

[csh8428]

@J-Riedel Here's a YXMD instead of a YXZP. You can't "export" to a different version of Alteryx, but here's a little trick to open workflows that were developed in newer versions of Alteryx. It will work as long as the tools inside the workflow aren't version specific and there's nothing crazy going on in this workflow so it will work.

How to "change the version"

1. Right click on the YXMD file.
2. Select "Open With"
3. Pick any text editor.
4. Change the version # to your version.
5. Save the file