How do I map a data dictionary to coded data?

Question

I have a massive dataset from a government entity that I am trying to analyze. The data dictionary is about 22k rows. All of the data in the dataset is coded like this: coded data sample

Typically, I've worked with small datasets and have just edited the alias in Tableau to reflect the coded value. However, this is not feasible with this dataset.

Sample of the dictionary: dictionary sample

I can't join this dictionary to the tableau file because the "name" column is the column name in the data set. The "code" column in the above screenshot is the code that would appear in that column and the code values is what I want to be able to display to the end user.

I've been stuck. I've tried joining, but it's a column/row mismatch. I've searched the tableau forums and the stackoverflow forums without success.

Answer 1

Aliases are still a good solution to be able to display meaningful headers and titles, while still working with efficiently coded data.

It sounds like you are just looking for a way to create the aliases from the code spreadsheet automatically, to avoid the effort of typing them in by hand. Understandable if that's your goal.

Tableau has a feature that you can use to effectively import a list of aliases for a field. The feature is called Edit Primary Aliases" https://help.tableau.com/current/pro/desktop/en-us/multipleconnections_edit_primary_aliases.htm

Working one field at a time. It's a little clumsy to but the basic approach is

1. Create a spreadsheet with two columns that lists the (short) codes and (long) aliases for the field in question. The column titles could be called Code and Description or Value and Name or whatever, but the first column name should be the same as field that you're working with.
2. Use the original data source as primary, create a secondary data source based on the spreadsheet you just made. Learn about data blending if the terms primary and secondary are not familiar to you.
3. Follow the approach shown in the Tableau help above for editing primary aliases.

Repeat the steps above for each field that you want to load the aliases for.

Note -- In this case, you're not using data blending in your final work, just using data blend temporarily as a way to import aliases from a spreadsheet to avoid typing them by hand.

Alternatively, you make lookup tables that translate codes to long names - one table per field, just as above. But instead of importing aliases, you could join with the lookup tables.