I feel like I'm coming at this from an Excel mindset, and I just can't figure it out. I couldn't find previous questions that are similar, but his must be pretty common so I must be just missing something pretty fundamental.
I have one table where one column has the folder location of files. Within the folder location there is a folder with the company user ID, the folder might have subfolders, and the path to the folder could vary greatly, eg:
| Folder |
|---|
| C:\Users\JoeSchmoe\UploadDocuments\Users\ MS123456 |
| C:\Users\JoeSchmoe\UploadDocuments\Users\ MS456789 |
| C:\Users\Alice\Desktop\ MS123456 \issue |
| C:\Users\Banana\Downloads\Files\ Temp-999999 \fixed |
| \\usny-abcd-efgh\Informatica\ipe\SrcFiles\BusinessInput\ MS456789 |
where the bolded folder is the user name
I have a separate table where each company user ID has the associated names
| User ID | Name |
|---|---|
| MS123456 | Dracula, Count |
| MS456789 | Doo, Scooby |
| Temp-999999 | Addams, Wednesday |
What I'm trying to do is to find a way in Spotfire to link or transform or something these two tables in such a way that in my list of folders I can clearly see the user name, like this:
| Folder | Name |
|---|---|
| C:\Users\JoeSchmoe\UploadDocuments\Users\ MS123456 | Dracula, Count |
| C:\Users\JoeSchmoe\UploadDocuments\Users\ MS456789 | Doo, Scooby |
| C:\Users\Alice\Desktop\ MS123456 \issue | Dracula, Count |
| C:\Users\Banana\Downloads\Files\ Temp-999999 \fixed | Addams, Wednesday |
| \\usny-abcd-efgh\Informatica\ipe\SrcFiles\BusinessInput\ MS456789 | Doo, Scooby |
I'm stuck in an Excel mindset, where I would do a VLOOKUP or XLOOKUP and to match any existing user ID in the folder path.
I can't do this in Spotfire because I can't do a transformation before linking both my tables, and I can't link both my tables because it doesn't recognize that the folder and user ID could be matched (understandably). The variance in the folder path makes it impossible (for my puny brain at least) to isolate the user ID to enable linking the folder column to the user ID column from my second table.
If the folder structure were regular it would be easy. In your case, the user id is not necessarily the last item in the folder structure.
I can think of a way that does not involve scripting: but it goes via a full join.
Use the recommendations in here to perform a full outer join of the two tables: Cross Join on two different tables in Spotfire
-first create a new calculated column called e.g. cross_join_criterion in both 'data' and 'folder' tables, and assign a single value (e.g. 1) -create a new table that starts from the 'data' table (select + then choose from 'Other': Linked copy to data table in analysis) -go to the data canvas for this new table -click on the plus sign to the right of the table, and choose 'add columns' -choose to join the 'folder' table, join by cross_join_criterion and choose join type = full outer join
The result contains all possible matches, some of which are not correct. Add a calculated column called e.g. Valid with this lookup expression:
This will be True only for the correct joins (unless you have two different user id's that contain each other). If this is too involved (you would still need to filter out the mis-matches) I would recommend generating a new table with a Python or R data function.