|Linking Tables and Worksheets ||
When linking tables in 1010data, the data type of each column being linked in the base table must match the type of each corresponding column being linked in from the foreign table.
Although 1010data will allow you to link on two columns that have differing types, the results may not be what you may expect. The reason is that because the two columns have different types, there will be no matches on which to base the link. The easiest solution is to create a computed column in one of the tables that converts a column from one data type to another so that the expected matching will occur.
For instance, a zip code column may be of type integer (i) in one table and type text (a) in another. Though it may look like these two columns have common values to link on, they will not match because they are different data types.
To find out the data type of a column, simply click the question mark icon () above the column heading. Below is column information for two zip code columns from two different tables, one of type integer and one of type text:
You cannot properly link these tables together using these columns because the data types do not match. Since zip codes often have a leading 0, converting the integer column to a text column is a better solution than converting the text column to an integer column. This conversion (in coding parlance, a type cast), is done using the string(X) function.
To create a computed column that will contain the converted values, click Value Expression in that dialog (specifying the zip column for the X argument):and enter the string(X) function for the
However, this creates another issue. Since integers may not contain leading zeros, some of the string values in the new computed column will only contain four digits instead of five. This is illustrated in the screenshot below:
To avoid this issue, we can use the function strembed(X;N;P;Y;D) in conjunction with the string(X) function when we create the computed column.
By specifying '0' for X and 5 for N, strembed(X;N;P;Y;D) creates a template of five 0's. We specify -1 for the P parameter, which is the index position where the zip code string (specified by Y) will be inserted into the template. Since it's a negative number, the last character in the zip code string will be positioned at the end of the template. This way, if the zip code string has four characters, one leading zero will remain from the template. If the zip code string has five characters, no leading zeros will remain. We set D to -1 so that strembed(X;N;P;Y;D) will return an N/A for any zip codes greater than five digits.
So, the following is the preferred method of creating the computed column for the type cast:
And now the 4-digit zip codes correctly have leading zeros:
You can then specify the new computed column in the Link in Another Worksheet dialog:
After clicking the Submit button, the information from the worksheet will be properly linked into the current table for those rows in which the information for the specified columns match.