Matching calculated column
I was recently able to join two tables (Table1 and Table2) by matching a calculated column (string) from Table1 to a normal string column in Table2. The next day, I replace my data table, and I am now unable to match Table2 columns to any kind of calculated column from Table1! The calculated column in Table1 is also matched to its corresponding column in Table2 via Manage Relations/Column Match. What happened? Thanks in advance.
Thanks for the responses!
Manjoj -- Not sure what you mean by data type? They are both imported data tables (from clipboard), and both columns are string values.
Bill -- I've heard that, but somehow it worked for me before! Also I found this post from 2012 where someone else had the same problem, but another person did not: http://spotfirecommunity.tibco.com/community/forums/p/1864/15120.aspx#15120
I did not understand what the user's "work around" was though. The problem with freezing the column is that the frozen column will be removed when you replace the data table with new data. I will make a duplicate column and freeze that one, but yeah -- totally a hassle!
This should work on its own with a couple modifications. Start from Table1, as you bring in this table add the join column as a calculated column from the transformations option. Add Table2 as a separate data table and add the join column as a transformation step to this table too. Table1 and Table2 should each have the calculated join column. The columns were derived on import and are column type = Imported, not column type = Calculated - no need to freeze.
Add a third data table from analysis using Table1 as the seed, we will call it Table3. From Table3, insert the columns you want from Table2 matching on the join column. The next time you replace Table1 include the calculated column in the transformation step and refresh Table3. Should work ...
Thanks for the help!
Since I am often replacing/updating Table 1 -- will I also have to "re-combine" the data tables each time I update? Or will the combined data table also update on its own?
What I am currently doing is freezing a second version of my calculated column in Table 1, then adding Table 2 to original Table 1. Unfortunately, the frozen column gets deleted each time I update Table 1, so I have to do this whole procedure each time I update, which is a hassle.