Dynamic filtering the data on based on conditions.

[Radhika]

Hi All,

Can anyone help me with this scenario?

I have one input as a database, below is its format. I have some filter conditions to get data.

Each condition can vary on different columns of input with 'and' and 'or' conditions and in the output, I need 3 columns - Eid (from input ), Category, Code (created in the canvas based on conditions defined),

Required: I need to create an optimized alteryx solution (might be a batch macro) where I can have all the columns of the input and every time a new condition is given, I just need to add 'AND' and 'OR' conditions to those particular columns mentioned in the condition and generates a new output of 3 columns (Eid, Category, Code)  in the same canvas in which Category and code are generated during the flow as per the condition. Hardcode values will be given for Category and Code in every defined condition. 

Example : 

Input sample

Eid	Process_date	Dept	Assigned_team	Location	Sal_currency	Base_location	Unit	Manager_Name	Days
1	20-07-2022	A	AA	Z	CAD	ZZ	avhj	AB	1
2	21-07-2022	B	BB	Y	INR	YY	yahj	VBF	2
3	22-07-2022	B	CC	X	JPY	TT	avh	HG	3
4	23-07-2022	D	DD	W	USD	WW	kgs	SJ	4
5	24-07-2022	E	EE	V	CAD	VV	njh	AJ	5

Let's say I have condition 1 for 1st flow -

Dept =A and sal= CAD then category = 'def' and code ='11'

Output - Output should be

Eid	Category	code
1	def	11

Condition 2 :

Dept=B and (assigned_team = bb or cc) and (unit = YY or TT) then category = 'GHI' and code ='22'

the output should be ;

Eid	Category	code
2	GHI	22
3	GHI	22

The only constant column in every condition is 'Dept' then further with other columns.

Each record from input will validate each condition and give output for each condition. Each condition should produce its own output of 3 columns or  combined output will also work

Conditions may use any string functions on any column (for eg left(assigned_team,2)).

Here I have given two conditions only but there'll be around 100 so I need an optimised and automated data driven solution for this so that every time a new condition is added, I have to just add 'AND' and 'OR' conditions to the particular columns and gets output.

If anyone can help, Thanks in advance.

[mceleavey]

Hi @Radhika ,

This is a simple combination of the Formula tool and the Filter tool:

The formula tool builds the logic to determine the Category and Code (I changed it as you appear to have made a mistake when you said Unit = YY or TT, but I think you meant Base_Location):

This populates the two columns, I then applied a filter to remove those records with blank Categories and Codes:

I think it would help if you learned more about the Formula tool and the Filter tool. You can find more information at the links below:

I hope this helped,

M.

https://help.alteryx.com/20221/designer/formula-tool

https://help.alteryx.com/20221/designer/filter-tool