topic Re: Delete Duplicate Data from Table using Time Stamp in Discussions

Delete Duplicate Data from Table using Time Stamp

Yngeinstn — Tue, 26 Jun 2018 19:07:51 GMT

I apologize, i know this question has been asked quite a few times on this forum but I haven't been able to find a solution to my problem.

I have a large data set ~24,000 rows of test data. Our process is to run a 5 step validation on 5 die and once we get the results we begin testing which ultimately means i am going to have repeat data on those 5 sites. This 5 step validation verifies the contact points which sometimes results in tearing down our test equipment and setting it back up. I am looking to exclude the duplicate data from the 1st test (touchdown). Our tests are timestamped so it is easy to distinguish between old and new. If anyone has a great solution to my problem i am all ears. Attached is the data table where i am trying to remove the duplicates.

Thank You,

Yng

Re: Delete Duplicate Data from Table using Time Stamp

txnelson — Tue, 26 Jun 2018 19:39:06 GMT

I am having trouble identifying from your attached data table, the rows that contain the data for Test 1 that you want to have deleted. Could you please provide the row numbers for 1 or two cases that represent the target data?

Re: Delete Duplicate Data from Table using Time Stamp

Yngeinstn — Thu, 28 Jun 2018 16:07:42 GMT

Sorry about that.. I deleted the unnecessary columns and reattached.

test_output is the data that i need. RowCol is the identifier of the devices i am testing.

I attached the summary table of the RowCol {r2c3sr1sc1, r2c8sr1sc1, r4c5sr1sc1, r7c3sr1sc1, r7c8sr1sc1} have the duplicate data. I need to delete the initial tests of those devices from the data set. They can be will have the earliest Time Stamp.. These 5 devices are used in every set-up which will require me to remove from each test_output.

Re: Delete Duplicate Data from Table using Time Stamp

txnelson — Wed, 27 Jun 2018 02:03:03 GMT

I think this will do the trick....it simply finds all of the rows for a given RowCol that have a timestamp(ts) less than the maximum timestamp for that RowCol, and then deletes them. It takes a couple of minutes to process, but it seems to work

Names Default To Here( 1 );
dt = Current Data Table();

// remove formula leaving static data
try(dt:RowCol << delete formula);

Summarize( dt, byGroup = by( "RowCol" ) );

For( i = 1, i <= N Items( byGroup ), i++,
	theRows = dt << get rows where( dt:RowCol == byGroup[i] & :ts < Col Max( dt:ts, :RowCol ) );
	If( N Rows( theRows ) > 1,
		dt << delete rows( theRows )
	);
);

Re: Delete Duplicate Data from Table using Time Stamp

Yngeinstn — Tue, 26 Jun 2018 23:35:07 GMT

Thank you so much! I am absolutely amazed at the skill you gentlefolk have with regards to scripting.. Here i was thinking that it was going to be some elaborate script that would accomplish this and you manage to get it in 10 lines..

You and Mr. gzmorgan0 have been extremely helpful..

Side note: my company has a P.O in the cycle that gets us JMP 14 which makes me super excited..

Thank you again Mr. txnelson..

Yng (not so Yng anymore)

Re: Delete Duplicate Data from Table using Time Stamp

Yngeinstn — Tue, 26 Jun 2018 23:43:30 GMT

I am curious! I am an Industrial Engineer and coding is very difficult for me.. If() and For() have thrown me for a loop (forgive the pun) since the beginning. How were you able to determine 9 as the number?

Thanks Again

Re: Delete Duplicate Data from Table using Time Stamp

txnelson — Wed, 27 Jun 2018 00:53:16 GMT

Oops....that should be a 1 not a nine

Re: Delete Duplicate Data from Table using Time Stamp

Yngeinstn — Fri, 29 Jun 2018 12:25:26 GMT

txnelson,

One more question regarding removing duplicate data.. I forgot to mention that i do a blanket data pull that includes all wafers in order for me to do an overall distribution and one way t-test analysis.

The data table that i attached was just the test data for one wafer.. Where in the script would i put the wafer_number? Would i include it in the summarize() part?

I have attached the new data table for reference.

I had to delete the other one because it was pointed out to me that i had my sql script that pull from our servers...

Re: Delete Duplicate Data from Table using Time Stamp

txnelson — Fri, 29 Jun 2018 18:48:50 GMT

I rethought through the issue, and came up with a totally methodology. This code will work for wafers

Names Default To Here( 1 );
dt = Current Data Table();

// Summarize the data to find the maximum :ts for each wafer/rowcol
dtSum = dt << Summary(
	Group( :wafer_number, :RowCol ),
	Max( :ts ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	link to original table( 0 )
);

// Join the data together matching only where the max :ts for each
// wafer/rowcol
dtFinal = dt << Join(
	With( dtSum ),
	Merge Same Name Columns,
	By Matching Columns( :wafer_number = :wafer_number, :RowCol = :RowCol, :ts = :ts ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 )
);
dtSum << delete columns("N Rows");
Close( dtSum, nosave );

// Delete all rows that do not match the maximum value of :ts within
// each Wafer, RowCol
dtFinal << select where( Not( :Match Flag == 3 ) );
dtFinal << delete rows;

// Get rid of the Match Flag column which is an artifact of the Join
dtFinal << delete columns("Match Flag");

Re: Delete Duplicate Data from Table using Time Stamp

Yngeinstn — Sat, 30 Jun 2018 12:34:51 GMT

Thank you txnelson,

I am going to try this out this morning and will incorporate it in my server pull script / table manipulation.

Much appreciated again..

Re: Delete Duplicate Data from Table using Time Stamp

Yngeinstn — Mon, 16 Jul 2018 19:03:50 GMT

Good Afternoon Mr. txnelson,

I thought i might reach out to you here instead of creating a new thread. I am needing some assistance calculating yields and generating a pick list / wafer map using the output table from the script you provided deleting the duplicate data. I looked through the community forums and found a post that was started back in 2016 from what i can see. It is somewhat confusing and hard to follow with all the output variables and such. I haved attached the tables that are the final output.

I have tried using split, stack, tabulate, summary and can't seem to come up with a solution. I actually have two separate tables that are identical except for a column named :trmode. As i mentioned i am trying to run yield analysis and then create a pick list and a wafer map.. I actually have another test_output table that i am currently working on using the tools that you fine gentlemen have provided to me..

dummy_data_tx: test_output have spec limits of (LSL = 20 and USL = 40) for each channel

dummy_data_rx: test_output have spec limits of (LSL = 15 and USL = 35) for each channel

dummy_data_dc: test_output have spec limits of- (actually having issues with deleting the duplicate data with this test..

set_id (7000) - VDD_Dig (LSL = 0.03 and USL = 0.06)

set_id (7100) - VDD_Dig (LSL = 0.03 and USL = 0.06)

set_id (7200) - VDD_Dig (LSL = 0.03 and USL = 0.06)

Any assistance you can give me again, is greatly appreciated..

set_id (7000) - ANALOG (LSL = 0.03 and USL = 0.07)

set_id (7100) - VDD_Dig (LSL = 0.2 and USL = 1)

set_id (7200) - VDD_Dig (LSL = 0.15 and USL = 0.35)