Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Stas
Level I

select where with couple of varaibles

Hello,
Need your help please. I have data table that I whish to filter by using "select where case. 
The filtering is by 2 parameters, one is numeric and the other is character. 

I don't understand why it's not working when I'm adding the character filter as well. 

Your help will be much appreciated.

Another small thing, in this function, to what do I need to change the combo box to get text box with ability to free text instead of choice list?

 

 

dt = Open( "\\Vmspfsfslc01\f28_pli\SQL\FE SQL\Data\M0R_tracer.csv" );

Summarize( a = By( :lot ) );
Insert Into( a, "<Select Traced lot>", 1 );
New Window( "Traced Lot",
    <<Modal,
    H List Box(

        cb1 = Combo Box(
            a,
            <<SetFunction(
                Function( {this},
                    selection = this << Get Selected();
                    r = dt << Get Rows Where( :lot == selection );
                )
            )
        )
    )
);

x = runkey[r];
y = entity[r];
dt << Select Where( :runkey > x & :entity == y );
dt << Delete rows;

 

4 REPLIES 4
Highlighted
dale_lehman
Level VI

Re: select where with couple of varaibles

I could be wrong (I don't do scripting, but I am confident someone will soon either confirm or correct me), but I think you need quotation marks around entity = y, i.e. entity = "y" for the nominal variable.

Highlighted
Stas
Level I

Re: select where with couple of varaibles

If i'll put quotation marks, it will refer it as the character y instead if variable
Highlighted
ThuongLe
Level IV

Re: select where with couple of varaibles

x and y in this case are list, so you cannot compare column value with a list. You have to get the value of the list to compare

dt = Open( "C:\Users\fs118919\Desktop\M0R_tracer.csv" );

Summarize( a = By( :lot ) );
Insert Into( a, "<Select Traced lot>", 1 );
New Window( "Traced Lot",
    <<Modal,
    H List Box(

        cb1 = Combo Box(
            a,
            <<SetFunction(
                Function( {this},
                    selection = this << Get Selected();
                    r = dt << Get Rows Where( :lot == selection );
                )
            )
        )
    )
);

x = runkey[r];
y = entity[r];
dt << Select Where( :runkey > x[1] & :entity == y[1] );
dt << Hide and Exclude;
Highlighted
txnelson
Super User

Re: select where with couple of varaibles

The code needed to be modified to handle the Get Rows Where() selection was specified.  Also, the Matrix returned from the Get Rows Where() so that only the 1st value is retrieved.

Lastly, the runkey on line 22 and the entity on line 23 needed to have the ":" added to them to indicate the specified are columns.  I tested the code out with your sample data table and it appears to work as you specified

names default to here(1);
dt = current data table();;

Summarize( a = By( :lot ) );
Insert Into( a, "<Select Traced lot>", 1 );
New Window( "Traced Lot",
    <<Modal,
    H List Box(

        cb1 = Combo Box(
            a,
            <<SetFunction(
                Function( {this},
                    selection = this << Get Selected();
                    r = (dt << Get Rows Where( contains(selection,:lot )))[1];
                )
            )
        )
    );
);

x = :runkey[r];
y = :entity[r];
dt << Select Where( :runkey > x & :entity == y );
dt << Delete rows;
Jim
Article Labels