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.
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