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

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

Mulitple Columns.yxmd