Applying A List to Multiple Columns

sagrim

I have a long list of certification acronyms stored as "mylist".  I am simply interested in creating a binary variable to represent whether each staff person possesses a certification whose acronym appears occurs in the list yes/no.  If I apply the list to one column the code works great.  

 

 

New Column( "Org-Approved Certification", Numeric, Nominal ) << Formula(
	If( Contains( mylist, :"ONE_LICENSE_CODE" ),
		1,
		0
	)
);

 

 

However, if I want to apply the list to several columns at once, the code runs without error but does not produce the correct value.  I have 10 columns any one of which which might be housing the acronym so I need to check all 10 columns for acronyms in the list.  

 

New Column( "Org-Approved Certification", Numeric, Nominal ) << Formula(
	If(
		Contains( mylist, :"ONE_LICENSE_CODE" ), 1,
		0 | If( Contains( mylist, :"TWO_LICENSE_CODE" )
		), 1,
		0
	)
);

 

Is there a way to point to multiple columns at once when applying the list?

Thank you!

SDF1

Hi @sagrim,

 

  I think you are on the right track, just a slight mis-coding in the "If" statement. In your specific example, I think it should be:

new column ("Org-Approved Certification", Numeric, Nominal) << Formula(
If( Contains (mylist, :"ONE_LICENSE_CODE") | If( Contains (mylist, :"TWO_LICENSE_CODE")), 1, 0
)
);

  So, in general, you want it to be:

If(A|B|C, then, else), but you had it more like: If(A, then, else | B, then, else), which is not the right way to call the If function.

 

  I made a mock data table that I think might be like the structure you are working with -- see attached file. Also, I wrote the following script that applies the formula to the organization approved certification. I believe this works as you intended -- I hope so!

names default to here(1);
mylist={"cert1", "cert2", "cert3","cert4", "cert5", "cert6"};
dt=current data table();
dt:Name("Org-App Cert")<<Formula(
	If(Contains(mylist,:"One_License_Code")|Contains(mylist,:"Two_License_Code")|Contains(mylist,:"Three_License_Code"),1,0)
);

Hope this helps!,

DS

 

Update: Another option that you could do is to do a FOR loop from i=1 to N Rows(dt) and just assign the number 1 or 0 to the approved certification column depending on whether or not the ith row of the jth column in the data table has an entry that is in the "mylist" column. Can you anonymize your data table and share it? It would be easier to work with that and write some code than guess what your data table structure looks like.

Untitled 40.jmp