Find Min from a Dynamic Number of Fields

[kaianderson]

I have a series of fields, "Date_Product1", "Date_Product2", "Date_Product3" etc. The number of these fields varies (needs to be able to handle 10 or 30 product fields). They each have a purchase date in them. I need to create a "First Product" field that pulls in the field name with the earliest date, whose calculation method can handle a changing number of fields.

Any ideas?

[jdunkerley79 · Accepted solution]

The easiest way to do this would be to:

1. transpose the data keeping the User as a Key field

2. Sort by User and Value

3. Sample the first for each user

4. Join back to input

Sample attached

[kaianderson]

Thank you so much! I had no idea that you could group on a sample tool. That was a huge help.

I was able to get this to work after inserting a filter between the sort and the sample tools to filter out records with null Value fields. Works like a charm!