Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
sagrim
Level II

Applying A List to Multiple Columns

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
DS
DS
Level VI

Re: Applying A List to Multiple Columns

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.

View solution in original post

3 REPLIES 3
Highlighted
DS
DS
Level VI

Re: Applying A List to Multiple Columns

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.

View solution in original post

Highlighted
sagrim
Level II

Re: Applying A List to Multiple Columns

This works perfectly. Thank you for taking the time to explain where I was going wrong. This helped me a lot:

"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."
Highlighted
DS
DS
Level VI

Re: Applying A List to Multiple Columns

Great! Glad it worked and it helped.

Article Labels

    There are no labels assigned to this post.