Find 2 values in each Line regardless of column name or no.

[PassION_es]

Hello Alteryx Community,

I would like to know if Alteryx has the capability to find values in each of the line item (condition 1 & 2 are both present) regardless of what columns they are located (because the columns is dynamica). That is, as long as all conditions are present, then that line item will be captured in the workflow and the output is to consolidate all those filtered/selected line items in their corresponding tab/sheet.

Attached here, is a sample of input and output (sheets highlighted in red).  The "Condition" sheet name contains the values or conditions that need to be present in each line.

[binuacs]

@PassION_es Can you explain the logic for the LOB tab has 6 records populated?

[PassION_es]

Hi @binuacs ,

Apologies, I updated the file to reflect the correct output for LOB.

Thanks

[binuacs]

@PassION_es would you mind explaining the logic of any one of the tab? I wan trying to understand the requirement but not getting it. 

[PassION_es]

@binuacs -The logic is that I do have thousands of datasets in this input file and also around 20 or more columns and I am trying to use two or more filter tools however, the filter values are not always permanent, meaning it can change the next month, as well as the column no. or column name.  So, as an alternative, I am asking the community if Alteryx can at least search for the 1st filter (which is in condition 1 column), then the next filter (which is in the 2nd condition column).  If both or if all is present, then it will be captured. In the first tab name (which is the "LOB"), there is only one filter that needs to be there in order for it to be captured and output in the LOB sheet.

I hope this clarifies. Thank you in advance.

[jdminton · Accepted solution]

I think this is doing what you are requiring. There are a few in your example output for LOB that did not match the input, but I don't know if that is because LOB is only requiring 1 criteria even though those input lines matched 2 criteria for a different categorization. I did not have them add to LOB because they matched another and did not think you'd want the duplication, but perhaps for your business case, you do want that duplicated. See attached.

 

[PassION_es]

Hi @jdminton , thank you. This workflow is what I am looking for.  For the LOB, it is correct that the workflow will not do any duplication. It was just that I have not think of this possible duplication when doing the dummy input file. Thank you.

[jdminton]

Great to hear! It should work fine as is, but you might be able to modify once you are plugged into actual data to shorten the workflow a bit.

[PassION_es]

Hi @jdminton , actually when i plugged to the actual data with many more than 20 fields/columns, there were only 3 tabs (out of 6 tabs) created. The missing results of 3 tabs were output to the "NO MATCH" tab. Those missing tabs have the same criteria located in the same fields (condition 1 and 2) with that of the INFRA tab.

Here are the missing tabs:

Chief Controls

ECR+SPCM

CAO

There should be the same total of outputs and inputs record counts, hence, duplicate can't be possible. Thanks.

[jdminton · Accepted solution]

@PassION_es Sorry about that. I had tried a simple way to work around the records that only matched the one condition (LOB). I switched it up a bit here. Try this workflow instead. There is one record in your sample, the very first, that does not meet the criteria for any, so it is assigned to the No Match tab in Excel.