Filtering data based on conditions

[ElA77]

I'm looking to create one record and fill out the rest of columns based on certain conditions. Is there a way to achieve this in Alteryx? I appreciate any advice. Please see attached file as an example.

[Qiu]

@ElA77 
OMG, I feel so powerless for the first time since I begin with Alteryx 🤣
I can only come up with a nasty flow to resolve your question.
Hope someone will come up something much more beautifully.

[LindonB]

@Qiu, I'm glad to you experienced the same. :) My solution is also a nasty workflow. 

@ElA77 , I believe that is because the filters are taking cells from around the table based on separate, unrelated filters. Still, it is doable.

My approach was to apply single filters and then select and rename fields matching that filter. This resulted in four filters.

I noticed two complexities:

First you want to add the working days to the date, not just the days. As far as I know, there's not a built in function to add working days. You could generate rows in Alteryx and then remove weekends, but that won't handle holidays. Instead, I add a text input with the working days from Excel to make the math work cleaner. The only limitation is that you might need to expand the range depending on the date range of your data. (I included all dates from 2024 and 2025.)
Second, you have multiple "Purch Ord" to bring in. I just pivoted them using a Crosstab tool and matched your formatting. 

[AndrewDMerrill]

It's always a pleasure to join other great minds to work toward solving problems ( @Qiu  & @LindonB ). By solving this problem in reverse, we are able to greatly simplify our workflow, with the added bonus of providing a more robust process. My approach was to calculate all the relevant columns via Formula Tool (using nulls for irrelevant data) and then Summarize everything at the end to arrive at the output.

Note: I adapted a very helpful formula for replicating the WORKDAY() function from excel, although it only disregards weekends and cannot handle holidays).

Replicating Excel WORKDAY Function in Alteryx - f... - Alteryx Community

Happy Solving!