Column Filters based on value of another column

[ArunKumarShukla]

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.

[AngelosPachis]

Hi @ArunKumarShukla ,

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

The output would be

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

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

[Brian32]

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

[jvansistine]

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.

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

[ArunKumarShukla]

Thnx AngelosPachis,

thnx for the quick reply and help

looking something like as I have data-

ID	Q1	Q2	Q3	Q4	month
1	0	7	9	8	1
2	4	0	9	7	2
3	7	9	7	6	3
4	6	8	8	5	4

if month= 1,2,3

then the output of the tool should be

ID	Q1
1	0
2	4
3	7
4	6

if month= 4,5,6

then the output of the tool should be

ID	Q2
1	7
2	0
3	9
4	8

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

Regards,

[AngelosPachis]

Hi @ArunKumarShukla ,

In your above example, you said

"

if month= 1,2,3

then the output of the tool should be

ID	Q1
1	0
2	4
3	7
4	6

"

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?

[ArunKumarShukla]

Thnx AngelosPachis

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

Company ID	Q1_amt	Q2_amt	Q3_amt	Q4_amt
0014	-27438	68439.9	-81646	-1449775
0014	0	0	0	-2327856
0029	-120407	12491.8	-7956.91	0
0032	-87402.8	0	0	0
0053	-3112.43	0	0	37701
0057	31200.69	0	0	0
0014	-6978513	-2312328	1566251	2267969

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

Company ID	Q1_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 ID	Q4_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

[AngelosPachis · Accepted solution]

That is perfect @ArunKumarShukla ,

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:

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.

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

Regards,

Angelos

[ArunKumarShukla]

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