topic Re: Select only tables which contain a specific string in the table to use as variable in Discussions

Select only tables which contain a specific string in the table to use as variable

Sburel — Tue, 19 May 2020 18:28:40 GMT

Hello,

I'm trying to concatenate a bunch of tables that are generated iteratively by a process.

The name of those tables all start with the same prefix.

I can easily concatenate all the open tables but I'm unable to find the correct syntax to only concatenate those tables with a specific prefix and leave the other ones alone.

Any suggestions?

Best

Sebastien

//The first script to concatenate only tables with 'Pvalue' in the name  fails
//But the dtLIst does shows the correct list of tables BUT list them without the 'data table' prefix.
// as a result, the concatenation step does not interpret them as a list of table
clear log();
Names Default To Here( 1 );
try(data table("LogP")<<close window,"");
dx=new table("LogP");
c = "Pvalues ";
dtLIst = {};

For( i = 1, i <= N Table(), i++,
	dt = data table(i)<<get name;
	if(contains(eval(dt),c),
	insert into(dtLIst, dt ))
);

print(dtLIst);
data table("LogP")<<concatenate(data table (dtLIst}),"Append To First Table");

//This scripts to concatante all the table works
//Shows the list of table with the 'data table' prefix
Names Default To Here( 1 );
try(data table("LogX")<<close window,"");
dx=new table("LogX");
dxLIst = {};

For( i = 1, i <= N Table(), i++,
	dt = data table(i);
	insert into(dxList, dt );
);

print(dxLIst);
data table("LogX")<<concatenate(eval(dxLIst),"Append To First Table")

Re: Select only tables which contain a specific string in the table to use as variable

txnelson — Tue, 19 May 2020 18:47:27 GMT

I can not see anything inherently wrong.....but you could be having a upper/lower case issue try

If(contains(uppercase(dt), uppercase(c),
insert into(dtLIst, dt )
);

Re: Select only tables which contain a specific string in the table to use as variable

Sburel — Tue, 19 May 2020 19:04:45 GMT

Hi Jim,

Thanks for the quick reply. I've tried you fix to no avail.

Here is the Log I get when I implement my script:

dtLIst =>

"{"Pvalues PBS vs PBS", "Pvalues PBS vs 741039", "Pvalues PBS vs 740410", "Pvalues PBS vs 709966", "Pvalues PBS vs 709878", "Pvalues PBS vs 709573", "Pvalues PBS vs 709547", "Pvalues PBS vs 709544"}

Cannot locate data table in access or evaluation of 'Data Table' , Data Table/*###*/(dtLIst)

at line 19 in C:\Users\sburel\Ionis\Neurology Research - Documents\Core\Neurotoxicology\Miscellaneous Research Activities\DATABASE ALL CNS DATA\Associated scripts\intermediate and toy scripts\Toy example to concatenate multiple tables with a specific string in nam.jsl "

If I use the next part of the script, here's the Log I get for dxLIst=>

"{DataTable("LogX"), DataTable("LogP"), DataTable("Pvalues PBS vs PBS"), DataTable("Pvalues PBS vs 741039"), DataTable("Pvalues PBS vs 740410"), DataTable("Pvalues PBS vs 709966"), DataTable("Pvalues PBS vs 709878"), DataTable("Pvalues PBS vs 709573"), DataTable("Pvalues PBS vs 709547"), DataTable("Pvalues PBS vs 709544"), DataTable("NT191209 7-DGE in vivo mouse single stacked")}"

The second list gets concatenated just fine but included tables I don't want. The first list (dtLIst) does not contain the "Data table ("xyz").

I suspect that I'm not using the proper syntax to tell the script to evaluate each items in the first list as a data table.

//This script to concatenate only table with 'Pvalue' in the name  fails
//But the dtLIst does shows the correct list of tables BUT list them without the 'data table' prefix.
// as a result, the concatenation step does not interpret them as a list of table
clear log();
Names Default To Here( 1 );
try(data table("LogP")<<close window,"");
dx=new table("LogP");
c = "Pvalues ";
dtLIst = {};

For( i = 1, i <= N Table(), i++,
	dt = data table(i)<<get name;
	if(contains(uppercase(dt),uppercase(c)),
	insert into(dtLIst, dt ))
);

print(dtLIst);
data table("LogP")<<concatenate(data table (dtLIst),"Append To First Table");

//This scripts to concatenate all the tables works
//Shows the list of table with the 'data table' prefix
Names Default To Here( 1 );
try(data table("LogX")<<close window,"");
dx=new table("LogX");
dxLIst = {};

For( i = 1, i <= N Table(), i++,
	dt = data table(i);
	insert into(dxList, dt );
);

print(dxLIst);
data table("LogX")<<concatenate(eval(dxLIst),"Append To First Table")

Maybe you'll be able to see the errors of my way :)

Thanks

Sebastien

Re: Select only tables which contain a specific string in the table to use as variable

txnelson — Tue, 19 May 2020 19:38:06 GMT

I apologize, I thought your issue was in building the dtLIst. The issue is slightly different.

The issue lies in providing to the Concatenate Platform the proper information. The structure of the Concatenate Platform's required input is

Data Table() << concatenate( data table(), data table()…., Append to first table(1) );

What you are passing to Concatenate is:

Data Table( "LogP" ) << Concatenate( Data Table( {"Pvalues PBS vs PBS", "Pvalues PBS vs 741039", "Pvalues PBS vs 740410", "Pvalues PBS vs 709966", "Pvalues PBS vs 709878", "Pvalues PBS vs 709573", "Pvalues PBS vs 709547", "Pvalues PBS vs 709544"} ), "Append to first table" )

The primary issue is that each of the data table names you have, need to be changed to the format of Data Table( "...….." )

This can be done with two small changes.

First, change the structure of your dtLIst. You don't want just the data table name, but the whole structure

For( i = 1, i <= N Table(), i++,
	dt = data table(i);
	if(contains(uppercase(dt),uppercase(c)),
	insert into(dtLIst, dt ))
);

Next, the Concatenate statement becomes

data table("LogP")<<concatenate( dtLIst,  Append To First Table(1));

Re: Select only tables which contain a specific string in the table to use as variable

Sburel — Tue, 19 May 2020 20:35:53 GMT

No need to apologize! I was probably not very clear in the first place.

Anyway, we must be close but the construction of the dtLIst is not working. I tried with with and without uppercase to no avail

Here's the log from the run:

argument should be character in access or evaluation of 'Uppercase' , Bad Argument( dt ), Uppercase/*###*/(dt)

So close, yet so far...

Here's my current code

clear log();
Names Default To Here( 1 );
try(data table("LogP")<<close window,"");
dx=new table("LogP");
c = "Pvalues ";
dtLIst = {};

For( i = 1, i <= N Table(), i++,
	dt = data table(i);
	if(contains(uppercase(dt),uppercase(c)),
	insert into(dtLIst, dt ))
);

print(dtLIst);
data table("LogP")<<concatenate(dtLIst, Append To First Table (1));

Re: Select only tables which contain a specific string in the table to use as variable

txnelson — Tue, 19 May 2020 20:52:36 GMT

The structure of what dt is has changed, so the contains needs to be changed to

contains(uppercase(dt<<get name),uppercase(c))

Re: Select only tables which contain a specific string in the table to use as variable

Sburel — Tue, 19 May 2020 21:20:04 GMT

That did the trick!

Thank you so much.

Best,

Sebastien