Column Filters based on value of another column

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.

Developer Tools

Accepted answers

AngelosPachis

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:

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

example.yxmd

All comments

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

example.yxmd

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

column selector based on month.yxmd

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

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:

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.

Screenshot 2021-01-13 201651.jpg

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

Regards,

Angelos

example.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703