Help identifying columns by their value

[yuvalshmul]

Hello!

I have two sets of data:

• weekly values for a set of 5000+ companies
• weekly values for a set of 50 industries

What I would like to do is identify a column based on a particular value in the industry data set, then go to that same column in the company data set and find the respective value for each company.

A mock example with dummy data below:

I have three industries, with one value per week for 5 weeks. Industry 1 and 2 each have 3 companies, also with one value per week for 5 weeks. What I want to do for each industry is find the week when the value first became positive, go to that same week in the company data, and for the companies in that industry mark down those values for that week.

So for example, industry 2 first become positive on week 1/7. When i look at the companies in Industry 2 (companies 4,5,6), their values for 1/7 are 3,8,3 - respectively - so that's what needs to be captures in the output. Note that i'm looking for the first time they become positive (as industry 2 becomes "positive" again on 1/28)

Now the issue is that each industry has a different date they become positive. If it were just a few industries and a few companies, easily to do manually. however, since i have 50 companies and over 5000 companies, i need to do this in a way that can easily be run iteratively for each industry

How should i go about this? 

Alteryx input / output attached

Thank you so much!

[apathetichell]

You are trying to apply a date/time logic to strings - you need to transpose and convert to strings. Without years this will quickly become a mess - but this will work in your sample data.

This would not work if you jumped years between min values. There is an alternative ways where you use your column order as values and match the lowest based upon that - but in the long run using dates and converting to dates is much much safer.