topic Column Character Matching Formulae in Discussions

Column Character Matching Formulae

RMSEBudgie341 — Tue, 27 Aug 2024 10:47:21 GMT

Hi All,

I have a dataset with 2 Columns, I just wanted to check to see if the data in Column 1 is matching with Column 2, It doesn't have to match row by row. All what i am interested in checking is to see if we have the same observations, The order in which they are in both the columns if not a concern.

Can anybody help me with a formulae to be used to check this?

Thank

Shawn

Re: Column Character Matching Formulae

txnelson — Tue, 27 Aug 2024 11:10:24 GMT

Here is an example of one way to do this

Names Default To Here( 1 );
dt = New Table( "Example",
	add rows( 100 ),
	New Column( "c1", set each value( Random Integer( 1, 1000 ) ) ),
	New Column( "c2", set each value( Random Integer( 1, 1000 ) ) )
);

dt << New Column( "Is in Both",
	formula(
		As Constant( c1Matrix = :c1 << get values );
		If( Length( Loc( c1Matrix, :c2 ) ) > 0,
			1,
			0
		);
	)
);

Re: Column Character Matching Formulae

jthi — Tue, 27 Aug 2024 11:43:26 GMT

You can also use associative array to compare the results IF you don't have decimals in your Column 1 and Column 2

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("Column 1", Character, "Nominal", Set Values({"A", "B", "C"})),
	New Column("Column 2", Character, "Nominal", Set Values({"AA", "B", "C"}))
);


dt << New Column("Diff", Numeric, Nominal, Formula(
	As Constant(
		aa1 = Associative Array(:Column 1);
		aa2 = Associative Array(:Column 2);
		aa1 << Remove(aa2);
		N Items(aa1) > 0;
	);
));