Dynamic Select; Select columns that contain a match to data in another field

[J-Riedel]

Hi,

I have a dataset that has multiple columns for each of the 4 quarters in the year, along with a field that shows the current quarter.  I am looking for a way to dynamically select the columns based on the Q# that is in the current quarter field.  I was thinking that I could do a Dynamic Select and use a formula, but I am unsure of what formula to use to try and match a portion of the column name to the data in another column.  Below and also in the attached file is my data, along with the view of the results that I am trying to achieve.  Any suggestions on how this can be done?

Thanks in advance!

DATA:

Activity Classification

Q1 QTD-Current Qtr Avg

Q1 QTD-Prior Qtr-Avg

Q1 QTD-Total Avg

Q2 QTD-Current Qtr Avg

Q2 QTD-Prior Qtr-Avg

Q2 QTD-Total Avg

Q3 QTD-Current Qtr Avg

Q3 QTD-Prior Qtr-Avg

Q3 QTD-Total Avg

Q4 QTD-Current Qtr Avg

Q4 QTD-Prior Qtr-Avg

Q4 QTD-Total Avg

Current Qtr

Activity 1

2

5

7

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

Q4

Activity 2

1

3

4

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

Q4

Activity 3

4

9

13

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

Q4

Activity 4

15

5

20

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

Q4

Activity 1

[Null]

[Null]

[Null]

22

6

28

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

Q4

Activity 2

[Null]

[Null]

[Null]

7

32

39

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

Q4

Activity 3

[Null]

[Null]

[Null]

8

8

16

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

Q4

Activity 4

[Null]

[Null]

[Null]

88

9

97

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

Q4

Activity 5

[Null]

[Null]

[Null]

10

1

11

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

Q4

Activity 1

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

45

3

48

[Null]

[Null]

[Null]

Q4

Activity 2

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

9

27

36

[Null]

[Null]

[Null]

Q4

Activity 3

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

5

33

38

[Null]

[Null]

[Null]

Q4

Activity 4

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

25

15

40

[Null]

[Null]

[Null]

Q4

Activity 1

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

8

8

16

Q4

Activity 2

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

19

4

23

Q4

Activity 3

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

49

2

51

Q4

Activity 4

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

33

12

45

Q4

Activity 5

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

18

12

30

Q4

Activity 6

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

[Null]

4

25

29

Q4

RESULTS:
If Current Qtr Field = Q2, only show the 3 fields that contain Q2 in the name
Activity Classification	Q2 QTD-Current Qtr Avg	Q2 QTD-Prior Qtr-Avg	Q2 QTD-Total Avg
Activity 1	22	6	28
Activity 2	7	32	39
Activity 3	8	8	16
Activity 4	88	9	97
Activity 5	10	1	11
If Current Qtr Field = Q4, only show the 3 fields that contain Q4 in the name
Activity Classification	Q4 QTD-Current Qtr Avg	Q4 QTD-Prior Qtr-Avg	Q4 QTD-Total Avg
Activity 1	8	8	16
Activity 2	19	4	23
Activity 3	49	2	51
Activity 4	33	12	45
Activity 5	18	12	30
Activity 6	4	25	29

[grazitti_sapna · Accepted solution]

Hi @J-Riedel , try the workflow attached as you provided the current quarter column so I leveraged that for my workflow if you need to calculate the current quarter on the basis of today's date let me know I will update the workflow. It is just one of the ways to solve the problem.

Thanks!

[Matthew]

this should do the trick.. and then after the dynamic select tool, just filter out the null rows

though of course i made the assumption that you are interested in the current quarter of the year, not the quarter that is in the "current quarter" column

contains([Name],"Q"+tostring(ceil(DateTimeMonth(DateTimeToday())/3))) or contains([Name],'Activity Classification')

[J-Riedel]

Thank you Sapna.  This works perfectly since it can be based on the Q# that I am already brining into the workflow.  I appreciate the quick response and help!

[grazitti_sapna]

@J-Riedel I am glad that I could help you and thanks for accepting my post as a solution. Much appreciated!