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 · Accepted solution]

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

[CoG · Accepted solution]

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!

[Qiu]

@CoG 
That is a very smart approach. Thanks😀

[ElA77]

Thanks for your help. I appreciate it.

[ElA77]

Thanks for your help. I appreciate it.

I do have a question in regarding using longest in Summarize tool, I'd like to use concatenate instead because there are situations where there are multiple answers, However, I only want to concatenate distinct values but I don't know how to go about it.

Thank you

[CoG · Accepted solution]

How many fields do you need to concatenate? If just one, then you can use two Summarize Tools, the first to "Group By" and isolate unique fields, then the second to perform concatenation.

If you have multiple fields that can be affected, then you will need to:

1. Transpose the data
2. Unique Tool to remove any duplicate values
3. Cross Tab to restore the table structure (using Concatenate as the aggregation method)

[ElA77]

Your proposed solution works for my need.

Thanks Andrew!