Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Search through multiple columns and output when value meets specific criteria

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

 

4 REPLIES 4
danilang
19 - Altair
19 - Altair

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

Aldona_S
7 - Meteor

@danilang worked like magic:) thanks a lot!

danilang
19 - Altair
19 - Altair

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

 

Dan

Aldona_S
7 - Meteor

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

Labels