community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

Search through multiple columns and output when value meets specific criteria

Meteor

Hello

 

I wanted to check what is the most efficient way to lookup through multiple columns with similar field names.

In the example file, I want to only output dates if DTn=1. Keep in mind in this file n is from 1-30 so there are about this many date type columns. Hence I want to avoid using the formula tool. The output column shows what the output should look like

 

 

IDDTDateDT2Date2DT3Date3DTnDatenOutput
1101/01/2018      01/01/2018
2102/01/2018      02/01/2018
3103/01/2018201/01/2018    03/01/2018
4104/01/2018202/01/2018    04/01/2018
5105/01/2018203/01/2018    05/01/2018
6  101/01/2018    01/01/2018
7  102/01/2018    02/01/2018
8  103/01/2018    03/01/2018
9  104/01/2018    04/01/2018
10  105/01/2018    05/01/2018
11  106/01/2018    06/01/2018
12    101/01/2018  01/01/2018
13201/01/2018  102/01/2018  02/01/2018
14202/01/2018  103/01/2018  03/01/2018
15203/01/2018  104/01/2018  04/01/2018
16204/01/2018  105/01/2018  05/01/2018
17205/01/2018  106/01/2018  06/01/2018

 

Nebula
Nebula

Hello Aldona

 

The trick here is create 2 separate transposed lists, one with the DATE* column names/values and one with the DT* names/values.  You then join them up on ID and column number.  This gives you one list with 3 fields ID, Date and DateFlag that a single Formula tool can process.  The Summarize compresses this list back to the original 17 rows with the Output column.  Join back to the original on ID if required

 solution.png

 

 

Dan

Meteor

@danilang worked like magic:) thanks a lot!

Nebula
Nebula

New marketing slogan for Alteryx.  Alteryx...I can't believe it's not magic!

 

Dan

Meteor

Yes! It is such a flexible tool. I am also impressed with how you came up with this, thanks again!

Labels