Data in a Different Language

When you get a dataset in a foreign language you may find yourself lost, how can you possibly analyse something you don’t understand? Well fear not in this blog post I’ll show you how to use the find and replace tool in alteryxto efficiently translate the data into your mother tongue. The German data set below will be used as an example for the blog.

Figure.1 Initial German Data

Step 1-  Translate Your Data!

Figure 2. Use Google Translate

Use your favourite translation tool to translate the words in you data set. Create a translation dictionary to keep track of each word.

Figure 3. Data Dictionary

Step 2- Get Your Data In the Right Format

Data should always be long and thin so use a transpose tool to convert the initial data source to be long and thin, as shown in Figure 4. Each of the fields in the data is a question. This can be transposed to get the data in the format shown on the right of Figure 4.

Figure 4. Transposed Data to be long and thin.

Step 3- DON’T DO THIS!!!!!!!!!

Sometimes it feels like writing an if statement formula is the quickest way to replace words in a field but don’t! The below diagram shows this. It searches every row for each word then if it finds it in the string it replaces it with the English translation. You shouldn’t do this because:

• It takes ages to manually add all of the ELSEIF statements
• It isn’t dynamic. If more words are added to the survey you have to add them into the formula

Figure 5. Horrendous IF statement do not do this!

Step 4- Use Find and Replace

Find and replace is the best way to do this. Using the translation dictionary alongside the find and replace allows you to efficiently translate the data.The advantages are:

• Can chose whether or not to be case sensitive.
• Can easily add more words to the translation dictionary.
• Can use the same translation dictionary for future projects.

The set up for the find and replace is shown below in Figure 6. The translation dictionary and data source are both brought into the find and replace tool. The user selects the field to search through within the data source for the values from the German word field of the translation dictionary. If any words are found, then the corresponding English word from the translation dictionary replaces the German word in the data, as shown in Figure 6.

Figure 6. Using Find and Replace to translate the Answer Field.

The same step is used to translate the question field, as shown in Figure 7. 

Figure 7. Using Find and Replace to Translate the Questions.