Hello,
I’m working on an add-in to horizontally concatenate values coming from OSIsoft-PI connector as it opens each Tag on a separate Tables for which Column (1) is the Timestamp & Column (2) is the Value of the Tag. I found a workaround using “Update” which works fine. However my initial plan was to use “Join” to avoid gathering rows when timestamps are misaligned and avoid the last step of deleting rows as with the “Update” strategy. Unfortunately, I faced various error messages and would like to understand better how to make the Join works (for further scripting works). Find attached a Word document with more details on steps and errors, as well as scripts (Update works, while Join fails) and datatables.
Thank you for the help, I'm open for a Team meeting or documentation.
Emmanuel
Bonjour, @Emmanuel_Romeu,
I don’t see the word document you mentioned.
Hi Emmanuel, You could try this, which loops through the join process and has only minimal expression handling.
Names Default To Here( 1 );
// List of opened files
file = Get Data Table List();
n_files = N Items( file );
joinCol = "Timestamp";
joinedDt = file[1];
For Each( {i}, 2 :: (n_files), // i =2;
Eval(
Eval Expr(
joinedDt = joinedDt << Join(
With( file[i] ),
By Matching Columns( Expr( Parse( Eval Insert( ":^joinCol^" ) ) ) = Expr( Parse( Eval Insert( ":^joinCol^" ) ) ) ),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 ),
invisible
)
)
);
//set first column to join column name
Column( joinedDt, 1 ) << set name( joinCol );
//delete working files and prepare for next time through loop
Close( file[i], nosave );
Try( Close( oldJoin, nosave ) );
oldJoin = joinedDt;
);
Close( file[1], nosave );
//remove extra join columns
For Each( {i}, N Col( joinedDt ) :: 2,
If( Lowercase( Left( Column( joinedDt, i ) << get name, 9 ) ) == Lowercase( joincol ),
joinedDt << delete columns( i )
)
);
joinedDt << show window( 1 );
Thank you Brady, it works.
Now I'll go to bed and tomorrow (or monday) I will try to understand exactly how it works 
.
Thank you.
Please let me know if you have any questions after looking it over, I will be happy to elaborate / have a call.
Cheers,
Brady
To fix Script_Concatenate_Tags_w_JOIN_Fail_2:
Change Line 70 to: Expr (dt_joined = dt_joined << Join(
Delete Line 80: Expr (dt_joined), current data table() ,
In my opinion, Current Data Table() should only be used to get the current table at the start of the script because it’s one of the few times you’ll know for sure what table it’s referencing (the last one the user touched). In many other circumstances the current table may differ from run to run depending on which operations are taking place, how long they take, whether the user has touched another table while things are processing, if changes in the underlying code have caused what was the current table in one version to change in the next, etc. The way you used Current Data Table() is why your script “worked” (It joined the second table from file with the table you selected). I strongly believe that tables should only be referenced explicitly. This is what is done in the new line 70, where you know for certain that it is both the table being messaged and the variable to which the resultant table is passed.
Getting column names into Platforms using expressions is tricky, particularly when the argument can take multiple columns. I almost always need to try a few different things before I find something that works. I’m fairly certain this functionality has evolved over the years, fortunately changing for the better (more flexible). Still, it’s a challenge. @Mark_Bailey has a nice write-up on working with column that I’d recommend reading.
In the Script_Concatenate_Tags_w_JOIN_Fail_1 script, the first Join block, the issue is not with Parse, but with attempting to insert a reference to the column into a list (JMP can’t find the column). While you can have multiple statements in Substitute (I do this frequently), you don’t need to in this case. You can put it directly in Join using its name instead of its number (which I was unable to get to work). On top of that, you can grab all the columns from the joined table and all but the first from the table to join it with. I’ve rearranged the code so I can explicitly reference the tables and columns and delete the table I don’t need immediately.
name = Get Data Table List(); <BR />For(i=1,i<=N Items(name),i++, name[i] = Head Name(name[i]));<BR /><BR />join1 = Data Table(1);
for (f=2, f<= N Items(name),f++,
colList1 = join1 << Get Column Names();
colList2 = Remove(Data Table(name[f]) << Get Column Names(),1);
Eval(
Substitute(
Expr (
dt_joined = join1 << Join(
With( join2 ),
Select(cl1),
SelectWith( cl2 ),
By Matching Columns( join1Col1 = join2Col1),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 )
)
) ,
Expr (join2), Data Table(name[f]),
Expr(cl1), Name Expr(colList1),
Expr(cl2), Name Expr(colList2),
Expr (join1Col1), Column(join1,1),
Expr (join2Col1), Column(Data Table(name[f]),1),
)
);
Close(join1,No Save);
Close(Data Table(name[f]),No Save);
join1 = dt_joined;
);
I've tidied things up at the top of the code, you don’t need to create a table to gather the table names. Much simpler putting the names into a list and working from that. This can be a bit unwieldy if the tables are big. Best to use Invisible or Private to hide the intermediate tables. Problem is that, as far as I know, there's no way to unhide the final table using messages. You could make a Subset using the entire table to reshow it.
Hello Don, thank you !!
*for Script_Concatenate_Tags_w_JOIN_Fail_2, as you said I used current data table () to make it works but I was suspecting that it is not the best way to do it. Now I understand that the solution was very close with dt_joined = dt_joined << Join() but I would never have thought about it by myself (now I understand "the table being messaged and the variable to which the resultant table is passed" I will hopefully think about it in the future).
*for Script_Concatenate_Tags_w_JOIN_Fail_1, indeed it is much simpler when using the right command (e.g. name[i] = Head Name(name[i] or colList2 = Remove(Data Table(name[f]) << Get Column Names(),1)
rather than building complex stuffs to achieve it (good to know).
Thank you again.
