Find Replace coding the same values from different columns

[andersom]

Hello

I've got a dataset with 100,000,000+ records across 22 columns of data but only 15 of them need to be decoded. I'm trying to use find and replace but some of the columns have the same value to complete the find and replace with i.e. 1,2,3, 4 etc

Example:

F_XMODE01 contains 3 values, 1 being part-time 2 being full-time and 3 being research

F_GENID contains 5 values so the find and replace codes 4, and 5 with a different value but assigns the results of 1, 2 and 3 as the values above

I also need to filter out my data as all results exceed the excel download size but can't filter it out before exporting due to the issue above?

HELP!!

[FinnCharlton]

Hi @andersom ,

You'll need one Find and Replace tool for each field you want to change. Because some values are shared between fields, you may be able to re-use the same look-up tables.

You should then be able to filter your results with the new values. Bear in mind that Excel has a 1M row limit, so you'll need to reduce your records quite a lot. Excel isn't optimised for this type of data storage - you could consider using a database.

Hope this helps!

[andersom]

Thanks for the reply,

I've used a separate FR filter for each variable I need to recode/label but it's using the same integer to code the data from multiple columns

The F_SEXID variable should only contain three results, M, F or Other but it's using the label from a different FR value BTEC Marker to populate the results with. There are shared values in some columns i.e., 1, 2, 3 etc where 1 should equal Male in the F_SEXID column but it's using 1 from the BTEC Marker column to label the F_SEXID column with?  

Any help or resolution would be a huge help!!

Mike.