Use value of one column to determine which column to pull another value

[stvnhdmpa]

Hello all,

I'm trying to figure out a way to pull the value from a column based on the value in another column.  Below is a dummy sample of data.  I have a "Months" column that represents the column number that I need the value from.  The New Value column is what I need in the end.  The real data set has 200 columns and over 2 million records.  I think I could transpose the number columns and do a simple if then, but this takes so long to calculate because it creates about half a billion records in the transpose.  

Months	1	2	3	4	5	6	7	8	9	10	New Value
1	0.001	1.001	2.001	3.001	4.001	5.001	6.001	7.001	8.001	9.001	0.001
2	0.002	2.002	4.002	6.002	8.002	10.002	12.002	14.002	16.002	18.002	2.002
3	0.004	4.004	8.004	12.004	16.004	20.004	24.004	28.004	32.004	36.004	8.004
3	0.008	8.008	16.008	24.008	32.008	40.008	48.008	56.008	64.008	72.008	16.008
4	0.016	16.016	32.016	48.016	64.016	80.016	96.016	112.016	128.016	144.016	48.016

Any thoughts?  Thanks in advance.

[Qiu]

@stvnhdmpa 

What is the logic for deciding your new values?

[echuong1 · Accepted solution]

You can pivot your data, so you get month-value pairs in a vertical list. From there, you can find the rows that have the month = name. I wrapped the values in tostring() just in case one of them were to be picked up as a string/number.

From there, it was simply a join on the record ID to bring back the original full dataset.

[Qiu]

@stvnhdmpa 

Sorry I think I just asked a stupid question.

Anyway, given the magnitude of your data set, it might be better to use a batch macro to level the computing load.

[stvnhdmpa]

@echuong1, After spending way too much time on this and finally posting this and stepping away, I figured out how I could get to the answer by changing the upstream process.  The main lookup table is 200x200 records, the source data is 2.2M where I was trying to join and then transpose, which ended with about half a billion records.  What I ended up doing is transposing the lookup table and joining on the month and "name" column.  It was taking over 5 minutes to process before and now it is taking 10-15 seconds to process.  Thanks!

[stvnhdmpa]

@Qiu  Interesting thought.  The package didn't seem to have the macro in the package.  Could you repackage with the macro?  I think I have the solution, but doesn't hurt to see the alternatives.

Thanks!

[Qiu]

@stvnhdmpa 
It is there named Month.