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
…
);
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"),
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?
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.
