topic Re: Script for select where using multiple columns and multiple variables in Discussions

Script for select where using multiple columns and multiple variables

ERobison — Fri, 09 Jun 2023 22:00:53 GMT

Hello,

I am trying to select multiple variables that can be found in multiple columns in the most efficient manner.

My columns are: CPT1, OTHERCPT2, OTHERCPT3....OTHERCPT7, CONCPT1.....CONCPT6

The variables I am looking for are various different CPT codes, for example: 58260, 58262, 58263, 58270, 58290,58291, 58292, 58294, etc.

I want to select where any of these variables exist in any of the columns.

Currently I have gotten the below script to work for selecting a single CPT code in all of the columns, however, I am trying to figure out the best way to look for multiple CPT codes in all of the columns.

thank you!

dt1 = current data table ();
dt1 << Select Where(
	:CPT == "58260" | 
	:OTHERCPT1 == "58260" | 
	:OTHERCPT2 == "58260" | 
	:OTHERCPT3 == "58260" | 
	:OTHERCPT4 == "58260"|
	:OTHERCPT5 == "58260" | 
	:OTHERCPT6 == "58260" | 
	:OTHERCPT7 == "58260" | 
	:CONCPT1 == "58260" | 
	:CONCPT2 == "58260" |
	:CONCPT3 == "58260" | 
	:CONCPT4 == "58260" | 
	:CONCPT5 == "58260" | 
	:CONCPT6 == "58260"
);

Re: Script for select where using multiple columns and multiple variables

txnelson — Wed, 16 Dec 2020 22:05:12 GMT

Here is how I would approach the issue

Names Default To Here( 1 );

dt = New Table( "example",
	Add Rows( 100 ),
	New Column( "one",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"3", "10", "1", "2", "5", "10", "2", "7", "3", "10", "6", "4", "10", "2", "5", "10", "8", "8", "5", "7", "3", "7",
			"5", "8", "10", "4", "2", "6", "5", "8", "3", "4", "4", "9", "9", "3", "4", "8", "3", "5", "5", "10", "10", "2", "2",
			"6", "3", "1", "7", "1", "9", "10", "8", "1", "6", "10", "3", "6", "9", "10", "2", "9", "2", "4", "8", "2", "6", "5",
			"1", "5", "4", "3", "10", "9", "9", "3", "10", "4", "3", "6", "1", "9", "6", "7", "5", "7", "1", "7", "6", "9", "3",
			"8", "9", "4", "2", "7", "6", "4", "6", "9"}
		)
	),
	New Column( "two",
		Character,
		"Nominal",
		Set Values(
			{"2", "9", "6", "10", "8", "10", "7", "5", "10", "1", "8", "2", "3", "4", "2", "8", "4", "6", "6", "2", "9", "1", "4",
			"7", "7", "6", "8", "3", "7", "9", "2", "1", "6", "9", "3", "7", "3", "10", "8", "10", "1", "5", "4", "1", "1", "9",
			"9", "10", "2", "7", "8", "5", "4", "9", "7", "3", "5", "10", "3", "8", "1", "1", "2", "5", "8", "10", "6", "1", "9",
			"3", "7", "4", "1", "8", "2", "1", "2", "2", "2", "10", "3", "2", "3", "5", "6", "7", "4", "9", "8", "4", "9", "5",
			"10", "3", "7", "3", "3", "9", "5", "3"}
		)
	)
);

findList = {"5", "9"};

dt << select where(contains( findList, :one) | contains(findList, :two));

Re: Script for select where using multiple columns and multiple variables

ERobison — Thu, 17 Dec 2020 15:52:08 GMT

Thank you! Works like a charm