Search through multiple columns and output when value meets specific criteria

[Aldona_S]

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

ID	DT	Date	DT2	Date2	DT3	Date3	DTn	Daten	Output
1	1	01/01/2018							01/01/2018
2	1	02/01/2018							02/01/2018
3	1	03/01/2018	2	01/01/2018					03/01/2018
4	1	04/01/2018	2	02/01/2018					04/01/2018
5	1	05/01/2018	2	03/01/2018					05/01/2018
6			1	01/01/2018					01/01/2018
7			1	02/01/2018					02/01/2018
8			1	03/01/2018					03/01/2018
9			1	04/01/2018					04/01/2018
10			1	05/01/2018					05/01/2018
11			1	06/01/2018					06/01/2018
12					1	01/01/2018			01/01/2018
13	2	01/01/2018			1	02/01/2018			02/01/2018
14	2	02/01/2018			1	03/01/2018			03/01/2018
15	2	03/01/2018			1	04/01/2018			04/01/2018
16	2	04/01/2018			1	05/01/2018			05/01/2018
17	2	05/01/2018			1	06/01/2018			06/01/2018

[danilang · Accepted solution]

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

 

Dan

[Aldona_S]

@danilang worked like magic:) thanks a lot!

[danilang]

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

Dan

[Aldona_S]

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