cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
UserID16644
Level V

Find and insert matching rows

I have two columns, ColumnA and ColumnB. Is it possible to put their matching value with Column B? Column B values are from this list (here's the snippet code)

fileNames = Files In Directory( directory );
newlist = {};
For( i = 1, i <= N Items( fileNames ), i++, 
	Insert Into( newlist, fileNames[i] )
);
dt << New Column( "COlumn B", Character, Values( newlist ) );

 

Column AColumn B
Apple.doc 
Orange.docOrange.doc
Grapes.docGrapes.doc
Strawberry.doc 
Mango.docMango.doc
1 ACCEPTED SOLUTION

Accepted Solutions
Georg
Level VII

Re: Find and insert matching rows

In addition to Join Tables from Jim this would be another solution:

Names Default To Here( 1 );

// Prepare Table with Column A
dt = New Table( "Column A",
Add Rows( 5 ),
New Column( "Column A", Character, "Nominal", Set Values( {"Apple.doc", "Orange.doc", "Grapes.doc", "Strawberry.doc", "Mango.doc"} ) )
);

// Get the list
newlist = {"Orange.doc", "Grapes.doc", "Mango.doc"};

dt << New Column( "Column B", Character );

// Check each row if value is part of the list
For Each Row( dt, If( Contains( newlist, :"Column A"n ), :"Column B"n = :"Column A"n ) );

edited just to make it more clear

Georg

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Find and insert matching rows

Create your Column B in a separate data table  and then use

     Tables=>Join

to merge the 2 tables together, matching Column A from  data table 1 with Column B from data table 2.

Jim
Georg
Level VII

Re: Find and insert matching rows

In addition to Join Tables from Jim this would be another solution:

Names Default To Here( 1 );

// Prepare Table with Column A
dt = New Table( "Column A",
Add Rows( 5 ),
New Column( "Column A", Character, "Nominal", Set Values( {"Apple.doc", "Orange.doc", "Grapes.doc", "Strawberry.doc", "Mango.doc"} ) )
);

// Get the list
newlist = {"Orange.doc", "Grapes.doc", "Mango.doc"};

dt << New Column( "Column B", Character );

// Check each row if value is part of the list
For Each Row( dt, If( Contains( newlist, :"Column A"n ), :"Column B"n = :"Column A"n ) );

edited just to make it more clear

Georg
jthi
Super User

Re: Find and insert matching rows

One more option, quite close to the one  @Georg provided

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(5),
	New Column("Column A",
		Character,
		"Nominal",
		Set Values({"Apple.doc", "Orange.doc", "Grapes.doc", "Strawberry.doc", "Mango.doc"})
	)
);

newlist = {"Orange.doc", "Grapes.doc", "Mango.doc"};

dt << New Column("Column B", Character, Nomimal,
	<< Set Each Value(
		If(Contains(newlist, :"Column A"n),
			:"Column A"n
		,
			""
		)
	)
);
-Jarmo
UserID16644
Level V

Re: Find and insert matching rows

Thank you all for your response. It works well. 
I just want to know what does n means in 

:"Column A"n
jthi
Super User

Re: Find and insert matching rows

It is something that has replaced Name() (in JMP16 I think). It is needed if there are some specific special characters for example in column names. Here is one help article which mentions it (there is better one, but couldn't quickly find it) Scripting Guide > JSL Building Blocks > JSL Syntax Rules > Names .

I used it because I don't trust spaces in my column names even though they should be ok in JMP.

-Jarmo