topic Re: Find and insert matching rows in Discussions

Find and insert matching rows

UserID16644 — Sat, 10 Jun 2023 23:51:31 GMT

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 A	Column B
Apple.doc
Orange.doc	Orange.doc
Grapes.doc	Grapes.doc
Strawberry.doc
Mango.doc	Mango.doc

Re: Find and insert matching rows

txnelson — Thu, 21 Jul 2022 08:49:38 GMT

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.

Re: Find and insert matching rows

Georg — Thu, 21 Jul 2022 17:34:40 GMT

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

Re: Find and insert matching rows

jthi — Thu, 21 Jul 2022 17:53:05 GMT

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

Re: Find and insert matching rows

UserID16644 — Fri, 22 Jul 2022 01:56:18 GMT

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

:"Column A"n

Re: Find and insert matching rows

jthi — Fri, 22 Jul 2022 05:08:22 GMT

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.