342508_en-US

liliconcom

Script for select where using multiple columns and multiple variables

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

Advanced Statistical ModelingAutomation and ScriptingQuality and Process EngineeringRe: Script for select where using multiple columns and multiple variables

Thank you! Works like a charm

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

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