249335_en-US

liliconcom

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></FORMULA>

Hope this helps!,

DS

 

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!

Re: Applying A List to Multiple ColumnsThis 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."
Re: Applying A List to Multiple Columns

Great! Glad it worked and it helped.