Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Community is experiencing an influx of spam. As we work toward a solution, please use the 'Notify Moderator' option on the ellipsis menu to flag inappropriate posts.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Find Replace coding the same values from different columns

andersom
6 - Meteoroid

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!!

 

 

  

2 REPLIES 2
FinnCharlton
13 - Pulsar

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!

 

FinnCharlton_0-1683123270133.png

 

andersom
6 - Meteoroid

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?  

 

andersom_0-1683188642551.png

 

Any help or resolution would be a huge help!!

 

Mike.

Labels