Subscribe Bookmark RSS Feed

Selection of Data

fuscod

Community Trekker

Joined:

Mar 18, 2015


I need to select all rows in at data set that meet this criteria:

CT<<Select Where ( (Is Missing (:Last Name)));   Note this is OK and not a problem.

The challenge I am having is I then need to DESELECT rows that meet the following criteria:

    

(  (is Missing (:Last Name) & Object Type == "NWA"  &  :Amount > 0)  )

This all seems simple enough but I've spent a couple of hours trying to figure it out!

Thanks,

Dan F.

7 REPLIES
ms

Super User

Joined:

Jun 23, 2011

CT << select where(Is Missing(:Last Name) & !(:Object Type == "NWA" & :Amount > 0));


ian_jmp

Staff

Joined:

Jun 23, 2011

The editor tooltips can be very useful in cases like this:

8391_Screen Shot 2015-03-19 at 09.23.03.png

By using the second parameter in 'Select Where' you should be able to get what you want:

NamesDefaultToHere(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

// Selects all males

dt << SelectWhere(:sex == "M");

wait(1);

// Deselects males who are 14

dt << SelectWhere(:age != 14, currentSelection("restrict"));

(I'm using JMP 12, BTW).

fuscod

Community Trekker

Joined:

Mar 18, 2015

JMP User Community – Thank you for your assistance on the JMP questions. I have another question for the User Community.

I am joining two jmp data tables by “Charge No” that originated from generic SAP Reports. The challenge is that:

· In Data Set A the “Charge No” data is: six numbers + 1 SPACE + four numbers

o For example: 141922 0010

· However in Data Set B“ Charge No” data is: six numbers + 7 SPACES + four numbers

o For example: 141922 0010

What I would like to do and my new question is how to write the jmp script that will modify Data Set B so that the configuration of “Charge No” the same as in Data Set A. i.e. six numbers + 1 SPACE + four numbers

Wendy_Murphrey

Joined:

Jun 23, 2011

Hi, Dan.

Regarding your spacing issue...  The Collapse Whitespace() function became available in JMP 11.  The following will eliminate duplicate spaces within the values located in the Charge No column. 

dtb = Data Table( "Data Set B" );

For Each Row(
     Column( dtb, "Charge No" )[Row()] = Collapse Whitespace( Column( dtb, "Charge No" )[Row()] )
);

Hope that helps!

Wendy

Wendy
fuscod

Community Trekker

Joined:

Mar 18, 2015

Thank you for this information – please see below for continuation of these questions:

1. For the condition (Is Missing(:Last Name) & Type == "NWA" & :Amount = $800)

I’d would like to populate the character column :Last Name == “Vertek Invoice”

2. And for condition (Is Missing(:Last Name) & Type == "NWA" & :Amount = $157,736)

I’d would like to populate the character column :Last Name == “LV2 PIN06/07”

Thank you,

Dan

Daniel Fusco

Project Manager

BWR In-Vessel Modifications and Repair

Westinghouse Electric Company

226 Airport Parkway, Suite 595

San Jose, CA 95110-3740

phone: +1 (408) 392-7455

cell: +1 (408) 202-3601

fuscod@westinghouse.com<mailto:fuscod@westinghouse.com>

pmroz

Super User

Joined:

Jun 23, 2011

dt = New Table( "Test Table", Add Rows( 6 ),

     New Column( "Last Name", Character, Nominal,

           Set Values( {"", "", "", "", "aaa", "bbb"} )

     ),

     New Column( "Type", Character, Nominal,

           Set Values( {"NWA", "NRA", "NWA", "NBA", "NWA", "NWA"} )

     ),

     New Column( "Amount", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [800, 800, 157736, 157736, 800, 157736] )

     )

);

v_rows = dt << get rows where(Is Missing(:Last Name) & Type == "NWA" & :Amount == 800);

if (nrows(v_rows) > 0,

     column(dt, "Last Name")[v_rows] = "Vertek Invoice";

);

l_rows = dt << get rows where(Is Missing(:Last Name) & Type == "NWA" & :Amount == 157736);

if (nrows(l_rows) > 0,

     column(dt, "Last Name")[l_rows] = "LV2 PIN06/07";

);

fuscod

Community Trekker

Joined:

Mar 18, 2015

Thanks!