Solved: Re: delete duplicate

RosDima · Feb 2, 2017 02:07 PM

Hi All,

I am trying to sort my Data Table, it hase duplicate values:

As you see, I have same value for different name in CHART_PARAMETER, my interest is to take only rows that have one type of CHART_PARAMETER per each value. I tryed Filter Data but it opens JMP window with options to choose, I am trying to do all calculations so user wont see them and create a new Data Table with selected rows and do furthe manipulation with this Data.

Thanks.

txnelson · Feb 2, 2017 02:31 PM

Here is a quick script that will return a data table where it contains only the chart_values that have one entry

Names Default To Here( 1 );
dt = Current Data Table();
dtsumm = dt << Summary(
	invisible,
	Group( :Chart_Value ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 )
);
dtsumm << select where( :n rows > 1 );
dtsumm << delete rows;
dtfinal = dt << Join(
	With( dtsumm ),
	By Matching Columns( :Chart_Value = :Chart_Value ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);
Close( dtsum, nosave );

Jim

View solution in original post

txnelson · Feb 11, 2017 09:06 AM

This is my error. In my rush, I indicated the wrong place for the trailing "num". Here is a little script that creates your Entity column and then add the new column with the counter in it.

Names Default To Here( 1 );
dt = New Table( "count",
	Add Rows( 34 ),
	New Column( "Entity",
		Character,
		"Nominal",
		Set Values(
			{"RLC101", "RLC101", "RLC101", "RLC101", "RLC102", "RLC102", "RLC102", "RLC102", "RLC102",
			"RLC102", "RLC102", "RLC102", "RLC102", "RLC102", "RLC102", "RLC102", "RLC103", "RLC103", "RLC103",
			"RLC103", "RLC103", "RLC103", "RLC103", "RLC103", "RLC103", "RLC103", "RLC103", "RLC103", "RLC104",
			"RLC104", "RLC104", "RLC104", "RLC104", "RLC101"}
		)
	)
);
dt << New Column( "Number",
	numeric,
	formula( If( Lag( :ENTITY ) != :ENTITY, num = 1, num = num + 1 );	
	num;
));

Jim

View solution in original post

txnelson · Feb 2, 2017 02:31 PM

Here is a quick script that will return a data table where it contains only the chart_values that have one entry

Names Default To Here( 1 );
dt = Current Data Table();
dtsumm = dt << Summary(
	invisible,
	Group( :Chart_Value ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 )
);
dtsumm << select where( :n rows > 1 );
dtsumm << delete rows;
dtfinal = dt << Join(
	With( dtsumm ),
	By Matching Columns( :Chart_Value = :Chart_Value ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);
Close( dtsum, nosave );

Jim

RosDima · Feb 7, 2017 03:22 PM

Hi,

Thanks for a quick reply.

Now I am trying to select last 3 point by date for each Entity. I can group Table by Entity but after that I need to find last 3 point by date range. How can I do it using select where or any other function.

RCL is my Entity and Entiry_1 names, and I need to select 3 last Values by date for each column. For example:

RCL101 3 last Values by date in Chart_Value and Chart_Value_1.

Will use any help:)

Thanks.

My Data table:

LOT_DATA_COLLECT_DATE	CHART_TYPE	MODULE	CHART_VALUE	ENTITY	MEASUREMENT_SET_NAME	MONITOR_SET_NAME	ENTITY_1	LOT_DATA_COLLECT_DATE_1	CHART_VALUE_1	MONITOR_SET_NAME_1	CHART_TYPE_1
2017/01/20 2:46:43 PM	X-BAR	RCL FE	8234.73	RCL104	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/20 8:25:35 PM	X-BAR	RCL FE	8734.13	RCL104	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/22 4:40:38 AM	X-BAR	RCL FE	8743.67	RCL104	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/22 3:51:54 PM	X-BAR	RCL FE	8539.85	RCL104	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/25 10:39:43 AM	X-BAR	RCL FE	9179.86	RCL104	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/28 9:03:46 PM	X-BAR	RCL FE	8097.51	RCL104	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/31 10:26:57 PM	X-BAR	RCL FE	7979.97	RCL104	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/02/07 6:53:08 PM	X-BAR	RCL FE	7992.35	RCL104	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/22 8:40:50 AM	X-BAR	RCL FE	7672.35	RCL103	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/27 7:54:09 AM	X-BAR	RCL FE	8319.25	RCL103	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/02/02 3:11:43 AM	X-BAR	RCL FE	7506.47	RCL103	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/02/06 10:42:00 PM	X-BAR	RCL FE	8046.7	RCL103	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/18 12:47:10 AM	X-BAR	RCL FE	81.0555	RCL102	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/20 12:35:05 PM	X-BAR	RCL FE	9159.32	RCL102	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/28 1:58:47 AM	X-BAR	RCL FE	9278.75	RCL102	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/02/03 4:38:46 AM	X-BAR	RCL FE	9288.21	RCL102	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/02/07 4:24:21 AM	X-BAR	RCL FE	8677.35	RCL102	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/21 7:22:14 AM	X-BAR	RCL FE	9969.63	RCL101	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/24 1:10:21 PM	X-BAR	RCL FE	9291.7	RCL101	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/01/31 1:16:24 AM	X-BAR	RCL FE	9521.71	RCL101	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
2017/02/05 4:41:46 AM	X-BAR	RCL FE	9287.35	RCL101	RCLFE.BATCH_OX_WTW.74.DER	RCLFE.BATCH_ASHRATE_PST.74.MON					
							RCL101	2017/01/21 7:34:05 AM	3897.38724	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL101	2017/01/24 1:28:26 PM	3898.22897	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL101	2017/01/31 12:17:43 AM	4002.58931	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL101	2017/02/05 5:01:06 AM	4014.63793	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL102	2017/02/03 4:41:11 AM	4001.03793	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL102	2017/02/07 5:03:23 AM	3927.14069	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL103	2017/02/02 5:45:08 PM	3831.54276	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL103	2017/02/02 5:49:14 PM	3827.88931	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL103	2017/02/03 3:24:54 AM	3786.87207	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL103	2017/02/06 11:49:29 PM	3847.12241	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL104	2017/01/20 2:49:09 PM	3960.13897	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL104	2017/01/29 1:54:30 AM	3988.62414	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL104	2017/01/31 9:58:15 PM	3984.87379	RCLFE.ASHRATE_PST.74.MON	X-BAR
							RCL104	2017/02/07 6:42:46 PM	4064.63414	RCLFE.ASHRATE_PST.74.MON	X-BAR

txnelson · Feb 7, 2017 03:42 PM

I think the solution is simpler than you think.

Sort the data by ascending Entity, descending date
Create a new column that has the following formula, which is a simple counter within each Entity

If( Lag( :Entity ) != :Entity,
			num = 1,
			num = num + 1
		);
		num;

Finally, you can Select Where your new column has a value of <= 3

Jim

RosDima · Feb 9, 2017 06:31 PM

Jim hi,

I tryed your script

Please see:

Table after code run.

Num didnt get proper numbers.

MP = AR << Sort(
by(ENTITY,LOT_DATA_COLLECT_DATE),Order(Ascending,Descending)
);

MP << New Column("num",
Character,
Formula(
	If( Lag(:ENTITY) != :ENTITY,
			num = 1,
			num = num + 1
		)
	)	
	);

Thanks.

txnelson · Feb 9, 2017 07:25 PM

The formula was not copied correctly. You left the trailing "num" off of the formula. The way formulas work, the last value expressed is the value the passed back to jmp. Therefore by specifying "num" as the last item in the formula, it is what gets passed back

MP = AR << Sort(
by(ENTITY,LOT_DATA_COLLECT_DATE),Order(Ascending,Descending)
);

MP << New Column("num",
Character,
Formula(
	If( Lag(:ENTITY) != :ENTITY,
			num = 1,
			num = num + 1
		)
	)	
	);
	num;

Jim

RosDima · Feb 11, 2017 5:19 AM

Hi,

I still have only one value "1" for each ENTITY.

MP = AR << Sort(
by(ENTITY,LOT_DATA_COLLECT_DATE),Order(Ascending,Descending)
);
//MP:LOT_DATA_COLLECT_DATE << Format("m/d/y")<<Data Type("Character")<<Modeling Type("Nominal") ;

MP << New Column("num",
Character,
Formula(
	If( Lag(:ENTITY) != :ENTITY,
			num = 1,
			num = num + 1
		)
	)	
	);
	num ;

Can I use any other way? What can I do wrong? Date should be in specific format?

Lag function compare EVENT for EVENT from previous row, how does it now that value to compare is the Date column?

Thanks.

txnelson · Feb 11, 2017 09:06 AM

This is my error. In my rush, I indicated the wrong place for the trailing "num". Here is a little script that creates your Entity column and then add the new column with the counter in it.

Names Default To Here( 1 );
dt = New Table( "count",
	Add Rows( 34 ),
	New Column( "Entity",
		Character,
		"Nominal",
		Set Values(
			{"RLC101", "RLC101", "RLC101", "RLC101", "RLC102", "RLC102", "RLC102", "RLC102", "RLC102",
			"RLC102", "RLC102", "RLC102", "RLC102", "RLC102", "RLC102", "RLC102", "RLC103", "RLC103", "RLC103",
			"RLC103", "RLC103", "RLC103", "RLC103", "RLC103", "RLC103", "RLC103", "RLC103", "RLC103", "RLC104",
			"RLC104", "RLC104", "RLC104", "RLC104", "RLC101"}
		)
	)
);
dt << New Column( "Number",
	numeric,
	formula( If( Lag( :ENTITY ) != :ENTITY, num = 1, num = num + 1 );	
	num;
));

Jim

RosDima · Feb 12, 2017 02:52 PM

Can you please send me information for Lag function if you have? I founded some examples but not in a way you used it.
Thanks a lot for your help!

txnelson · Feb 12, 2017 7:09 PM

Look in the Scripting Index

Help==>Scripting Index==>Functions==>Lag

Also, look in the book, Using JMP

Help==>Books==Using JMP

Lag

Returns the value of the first argument in the row defined by the current row less the second argument. The default Lag is one, which you can change to any number. The value returned for any lag that identifies a row number less than one is missing. Note that Lag(X, n) gives the same result as the subscripted notation, XRow( )–n.

Jim