nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

variable reference problem when looping through data tables

anders_bankefor

I’m trying to loop through a series of tables to update my main table with information, however for an unknown reason this won’t work. See code attachment below:

Sample from my List that stores my tables:

DTs = {Data Table( "Sheet1" ), Data Table( "Sheet2" )……}

//open excelfiles in a specified path, works fine

path = "$DOCUMENTS\580501\";

ftype = ".xlsx";

// Make list of files, remove all non-matching files, works fine

flist = Files In Directory( path );

For( i = N Items( flist ), i > 0, i--,

If( !Ends With( flist[i], ftype ),

Remove From( flist, i )

)

);

d = N Items( flist );

//import all excelfiles from directory 580501 and store them in a list, works fine

While( d > 0,

i = 1;

dtt = Open(….)

//this part of my code works fine

DTs = List();

s = 1;

For( i = 1, i <= N Table(), i++,

If( Data Table( i ) != "ISAC_STAT",

Insert Into( DTs, Data Table( s ) );

s = s + 1;

)

);

//Parsing through all open tables in DTs

For( i = 1, i <= N Table(), i++,

dtt = Data Table( DTs( i ) );

//I need to find one specific table “sheet1” and update before I can update against other tables

//sheet1 contains information needed to match against other tables so it has to update my main

//table first

For( k = i, k <= N Table(), k++,

//I Think this If statement is the source to my problem but I don’t know why?

//I have tried several versions of this If statement:

//Contains( Char( DTs[k] ), Char( "”Data Table( “sheet1”) “) == 1

// Contains(DTs[k], “sheet1”) == 1

// Contains(DTs[k], “Data Table( "Sheet1" )“== 1

//but nothing works.

If( Contains( Char( DTs[k] ), "sheet1" ) == 1,

Data Table( "My Table" ) << Update(

With( Data Table( "sheet1" ) ),

Match Columns( :Batch = :Lot ),

Add Columns from Update table( None )

);

);,

);

//I Think this If statement is the source to my problem but I don’t know why?

If(

DTs[i] != "sheet1" & DTs[i] != "sheet2" & DTs[i] != "sheet3" & DTs[i] != "sheet4",

Try(

Data Table( " My Table " ) << Update(

With( DTs[i] ),

Match Columns( :Batch = :Lot | :Batch = :Test Lot ),

Add Columns from Update table( None )

);

//I use the same update command as above but matching different columns

…

);

Accepted answers

David_Burnham

Here is a simple code snippet that works:

dt = Open("$SAMPLE_DATA/Big Class.jmp");
// dt = DataTable("Big Class");
If (Contains(Char(dt),"Big Class"),
	show("found")
,
	show("not found")
)

As Wendy points out the Contains function is case sensitive, so I would usually force the case e.g.

If (Contains(LowerCase(Char(dt)),"big class"),

All comments

Craige_Hales

the contains() function returns 0 if not found, or the position (often not 1) where the text was found. I'm unclear if "sheet1" should be found at position 1. Try
if( contains( a, b ), print("yes"), print("no") );
if you are not looking for a specific position. Or, compare equal or not equal to 0. You can also send some debugging output to the log window:
show( a, b, contains(a,b) );
gives a nice annotated answer.

Wendy_Murphrey

Hello,

In addition to Craige's point, there is a problem with the string you are searching for. When you insert values to the DTs list, you are inserting Data Table( "tableName" ). So your DTs list represents something like this:

{Data Table( "Sheet1" ), Data Table( "Sheet2" )} ;

Yet in most of your Contains() functions, you are searching for string values that do not share the same case or pattern of the items in the DTs list, such as "sheet1". This is the reason these Contains() functions return 0. In other words,

"Sheet1" != "sheet1"

The point here is that the search strings must be identical to the values you want Contains() to find.

anders_bankefor

Hi everyone and thank you for your quick answers,

As you said the DTs list stores strings with values like Data Table( "Sheet1" ) therefore I was sucessful in implementing the nested for-loop where I'm searching for two specific tables.

However in the later case when I don't want certain types of tables to be used, and maybe this is the error, I'm using the contains-functions to match any string value in DTs to a certain key word.

for example:

if contains (DTs[i], "soda") or contains (DTs[i], "water") or contains (DTs[i], "food_1"),

do nothing

else update main table.

Note that there are cases when we have both food_1 and food_2 table, here we should disregard food_1 but use food_2.

Am I using the contains-function incorrect?

David_Burnham

Here is a simple code snippet that works:

dt = Open("$SAMPLE_DATA/Big Class.jmp");
// dt = DataTable("Big Class");
If (Contains(Char(dt),"Big Class"),
	show("found")
,
	show("not found")
)

As Wendy points out the Contains function is case sensitive, so I would usually force the case e.g.

If (Contains(LowerCase(Char(dt)),"big class"),