Subscribe Bookmark RSS Feed

How to Fill Ill-Formatted Columns

engineerchris

Community Member

Joined:

May 30, 2014

I have a data table with 1.6M rows.  This data table was formed by concatenating many smaller data tables.  Unfortunately, these smaller data tables have gaps in many of the columns. For instance, there will be several thousand rows that were all written on the same date, but the date and timestamp is only filled in the top row of the smaller data table. This means I have a data table which has the date field filled-in on only every 9,000-12,000 rows, with everything in between being blank.

 

Is it possible to have some sort of smart fill where it fills in empty rows with a value until it reaches a non-empty row and then starts filling in subsequent rows with this new value?  It would take an eternity to do this by hand with the crappy fill-to-row command for each batch of 10K rows in my 1.6M row long data table.

 

 

HOW IT LOOKS NOW:

   Row #      Date      Thing ID      Data 1      Data 2   
1March 8, 2013   X001Attr.1Color.3
2  Attr.2Color.2
...  ......

9653

   Attr.9653Color.9653 

9,654

March 21, 2013   X002Attr1Color.1
9,655  Attr.2Color.2
...  ......
19,630  Attr.9975Color.9975
19,631March 30, 2013   X003Attr.1Color.1
19,632  Attr.2Color.2
..  ......

 

HOW I WANT IT TO LOOK:

   Row #      Date      Thing ID      Data 1      Data 2   
1March 8, 2013   X001Attr.1Color.3
2March 8, 2013   X001Attr.2Color.2
...March 8, 2013   X001......

9653

March 8, 2013   X001 Attr.9653Color.9653 

9,654

March 21, 2013   X002Attr1Color.1
9,655March 21, 2013   X002Attr.2Color.2
...March 21, 2013   X002......
19,630March 21, 2013   X002Attr.9975Color.9975
19,631March 30, 2013   X003Attr.1Color.1
19,632March 30, 2013   X003Attr.2Color.2
..March 30, 2013   X003......

 

1 REPLY
txnelson

Super User

Joined:

Jun 22, 2012

Here is a simple script that will do the trick

 

 

Names Default To Here( 1 );
dt = Current Data Table();
// for example I will call the column in question, "date"
For Each Row( 
	If( Is Missing( :date ) == 1, 
		:date = Lag( :date ) 
	) 
);
Jim