Alteryx Designer Discussions

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

Column Filters based on value of another column

5 - Atom

Hi, I have 06 columns- ID, Q1, Q2, Q3, Q4, and Month, based on month value like 1,2,3, I want to select ID and Q1 while if Month is 4,5,6 I wanna ID and Q2. Just tried using a dynamic filter but not work, any help will help me to understand how to filter columns.

Alteryx Certified Partner

Hi @arunshukla2000 ,

 

I've mocked up a workflow for you, which essentially transposes the dataset and filters out the values that fall within the correct quarter. So if your input looks something like this

 

AngelosPachis_0-1610475776632.png

 

The output would be

 

AngelosPachis_1-1610475809480.png

 

You could go on and bring your table to the original form having quarters in columns, so you get the correct value for each quarter

 

AngelosPachis_2-1610475847855.png

 

That's my take on the problem given the lack of provided input data, but feel free to share an alternative dataset if I didn't answer the question fully.

 

Regards,

 

Angelos

7 - Meteor

Good Afternoon - 

 

You could utilize Filter and Select in tandem.  Off of your data stream, filter on Month IN (1,2,3).  Then off of your True Output use a Select Tool to only pass through ID and Q1.  Connect another Filter and Select Tool to the data stream to complete this for Q2, and then again for Q3 and Q4.  If you then need this to all be pulled back together, use a Union Tool to to bring back in all 4 individual streams.

 

Hope this helps.

 

Brian

Alteryx Certified Partner

Hi arunshukla2000,

 

You could try creating a new column with a formula tool to match the correct "Q" column, and then add a select tool to keep just that new column.

 

jvansisine_0-1610476529074.png

 

A sample is attached of how I approached it, based on your description. But if you have sample data that is different that would be helpful.

Jim

5 - Atom

Thnx 

looking something like as I have data-

IDQ1Q2Q3Q4month
107981
240972
379763
468854

IDQ1
10
24
37
46

 

IDQ2
17
20
39
48

and for months 7,8,9 ID and Q3 and so on...

 

Regards,

 

Alteryx Certified Partner

Hi @arunshukla2000 ,

 

In your above example, you said

"

IDQ1
10
24
37
46

"

Also IDs 1,2,and 3 are for months 1,2 and 3 respectively, whilst ID 4 is for month 4.

 

Why ID 4 is contained in the suggested output above, since it does not belong in Q1?

5 - Atom

Thnx 

Here is the actual problem, we received the excel each month having quarter 1 to 4 data look like---

Company IDQ1_amtQ2_amtQ3_amtQ4_amt
0014 -2743868439.9-81646-1449775
0014 000-2327856
0029 -12040712491.8-7956.910
0032 -87402.8000
0053 -3112.430037701
0057 31200.69000
0014 -6978513-231232815662512267969

so using alteryx, business want if the current month is Feb then only Company ID and Column Q1_amt like

Company IDQ1_amt
0014 -27438
0014 0
0029 -120407
0032 -87402.8
0053 -3112.43
0057 31200.69
0014 -6978513

Should be populated not other columns, similarly if the month is Nov then 

Company IDQ4_amt
0014 -1449775
0014 -2327856
0029 0
0032 0
0053 37701
0057 0
0014 2267969

 

what I did, I have created a new column "Month" using DateTimeMonth(DateTimeToday()) formula which gives me a number of that month, and based on this I wanna filter out columns.

I am not sure this is the correct way, make me correct if not going well.

Regards,

Arun

Alteryx Certified Partner

That is perfect @arunshukla2000 ,

 

To dynamically select columns, you can use a tool called "Dynamic Select" which will allow you given a certain expression to drop/select some of your fields.

 

You have correctly identified that a part of the expression needed to be used is:

 

DateTimeMonth(DateTimeToday())

 

and we should check the results of that expression against values from 1-12 to define the quarter. The tool should be configured as shown below:

 

Screenshot 2021-01-13 201330.jpg

 

where you essentially select, given that DateTimeMonth(DateTimeToday()) numeric value, that you want to select all field containing Company ID and Q1 in their name.

 

So you can see in the results window, I have the desired result.

 

I've run a test if the date was the 1st of August 2020, and the tool appears to work correctly (only kept Q3 column, so I expect to keep working correctly in the future if your dataset always has a column names "Colmpany ID" and Q1-4.

 

Screenshot 2021-01-13 201651.jpg

 

Hope that helps, let me know if you have any questions.

 

Regards,

 

Angelos

5 - Atom

Thnx Bro,

this is what I was looking for since I am new and don't know how to use the expressions in dynamic tools.

really appriciate.

 

Arun

Labels