Linking on columns with different types

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, 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.

The code to create the computed column is as follows:

<willbe name="textzip" label="Zip as Text" value="string(zip)"/>

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, use the strembed(X;N;P;Y;D) function in conjunction with the string(X) function when creating the computed column, then subsequently link to one of the tables as a worksheet.

By specifying '0' for X and 5 for N, strembed(X;N;P;Y;D) creates a template of five 0's. 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. Set the D parameter to -1 so that strembed(X;N;P;Y;D) will return an N/A for any zip codes greater than five digits. This ensures that each zip code will contain no more than 5 digits, padded with leading zeros when necessary.

Here's an example of the full computed column expression:

<willbe name="fixziptext" value="strembed('0';5;-1;string(zip);-1)"/>

The code above will produce a computed column that fixes the issue, as shown below:

Now the link can be created as a worksheet link by using the newer computed column, as follows:

<link table2="certification.retail.stores" col="zipcode" col2="fixziptext" suffix="SM">
 <willbe name="fixziptext" value="strembed('0';5;-1;string(zip);-1)"/>
</link>