59555_en-US

liliconcom

Create an IF statement that allows for multiple result outputs

I am trying to filter out data based on a value in a column either a 1 or a 0. I can get it to filter out the 1 or the 0, but I would also like to be able to show all columns both 1 and 0. I have tried varying things and they either come out with a range issue or an issue with it not being completely specified. The user has the ability to input in a radio box if they want 1, 0 or both, but I cannot get both to show up without an error.

 

 

(Current Data Table() << Data Filter(
Mode( Show( 1 ), Include( 1 ) ),
Inverse( 0 ),
invisible,
Add Filter(
    columns(:Fine),
     Where(IF(V=="Coarse", :Fine==0, V=="Fines",:Fine==1,V=="Both",:Fine >=0 ))))), //Coarse, Fines or Both are selected in a previous code that works well.

 

 

 

 

 

 

Re: Create an IF statement that allows for multiple result outputs

My column (:Fine) is continous. If I understand correctly :height in this example is continous and you are setting a range. However, I am trying to set-up an "IF" statement in a "Where" statement. 

 

<SPAN class="token function">Current Data Table</SPAN><SPAN class="token punctuation">(</SPAN><SPAN class="token punctuation">)</SPAN> <SPAN class="token operator"><</SPAN><SPAN class="token operator"><</SPAN><SPAN class="token messages"> Data Filter</SPAN><SPAN class="token punctuation">(</SPAN>
 Location<SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">{</SPAN><SPAN class="token number">314</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token number">74</SPAN><SPAN class="token punctuation">}</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
 Add Filter<SPAN class="token punctuation">(</SPAN>
  columns<SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">:</SPAN>age<SPAN class="token punctuation">,</SPAN> <SPAN class="token punctuation">:</SPAN>height <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
  Where<SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">:</SPAN>age <SPAN class="token operator">==</SPAN> <SPAN class="token punctuation">{</SPAN><SPAN class="token number">12</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token number">14</SPAN><SPAN class="token punctuation">}</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
  Where<SPAN class="token punctuation">( If( x=="tall",:height >=72, x=="average", </SPAN> <SPAN class="token punctuation">:</SPAN>height <SPAN class="token operator">>=</SPAN> <SPAN class="token number">58</SPAN> <SPAN class="token operator">&</SPAN> <SPAN class="token punctuation">:</SPAN>height <SPAN class="token operator"><=</SPAN> <SPAN class="token number">66, x=="Short", :height <= "58"</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,  // x is selected previously in a radio box. </SPAN>
  Display<SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">:</SPAN>age<SPAN class="token punctuation">,</SPAN> Size<SPAN class="token punctuation">(</SPAN> <SPAN class="token number">160</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token number">90</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN> List Display <SPAN class="token punctuation">)</SPAN>
 <SPAN class="token punctuation">)</SPAN>
<SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>

If I do something like this. It comes up with an error that says the "Where" clause has not been correctly specified 2 times.   

Re: Create an IF statement that allows for multiple result outputs

Is your column named :Fine defined as a continuous column?  If it is Ordinal or Nominal your syntax is correct, however, if it is Continuous, the syntax is as listed below

Current Data Table() << Data Filter(
 Location( {314, 74} ),
 Add Filter(
  columns( :age, :height ),
  Where( :age == {12, 14} ),
  Where( :height >= 58 & :height <= 66 ),
  Display( :age, Size( 160, 90 ), List Display )
 )
);

Re: Create an IF statement that allows for multiple result outputs

Thank you for sharing this code with me. I tried the code in a separate script and see that it works the way I would like my code to. However it is not working for me, does this only work for nominal sets? I am still having a problem and with a missing range error.

 

The code now looks like the following:

(Current Data Table() << Data Filter(
Mode( Show( 1 ), Include( 1 ) ),
Inverse( 0 ),
invisible,
Add Filter(
    columns(:Fine),
     Where(: Fine =={0,1}))));

 

an error says that the range is missing. Is there a function where I can set the range of a Column or Variable? 

 

I have listed all of my columns in the "Columns" element and I have "Where" statements separate for all of my columns that I want to filter out.  

 

 

Re: Create an IF statement that allows for multiple result outputs

Below is an example taken directly from the Scripting Index.  It shows the syntax for selecting from multiple columns.  Please not that all columns that are to be selected on must be listed in the "Columns" element, and that the where clause needs to be set separatly for each column

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Cities.jmp" );
obj = dt << Data Filter();
obj << Add Filter( columns( :POP ) );
obj << Add Filter(
 columns( :Region, :State, :City ),
 Where( :Region == "S" ),
 Where( :State == {"SC", "NC"} )
);