33907_en-US

liliconcom

How to Fill Ill-Formatted Columns

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
1	March 8, 2013	X001	Attr.1	Color.3
2			Attr.2	Color.2
...			...	...
9653			Attr.9653	Color.9653
9,654	March 21, 2013	X002	Attr1	Color.1
9,655			Attr.2	Color.2
...			...	...
19,630			Attr.9975	Color.9975
19,631	March 30, 2013	X003	Attr.1	Color.1
19,632			Attr.2	Color.2
..			...	...

 

HOW I WANT IT TO LOOK:

Row #	Date	Thing ID	Data 1	Data 2
1	March 8, 2013	X001	Attr.1	Color.3
2	March 8, 2013	X001	Attr.2	Color.2
...	March 8, 2013	X001	...	...
9653	March 8, 2013	X001	Attr.9653	Color.9653
9,654	March 21, 2013	X002	Attr1	Color.1
9,655	March 21, 2013	X002	Attr.2	Color.2
...	March 21, 2013	X002	...	...
19,630	March 21, 2013	X002	Attr.9975	Color.9975
19,631	March 30, 2013	X003	Attr.1	Color.1
19,632	March 30, 2013	X003	Attr.2	Color.2
..	March 30, 2013	X003	...	...

 

Re: How to Fill Ill-Formatted Columns

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 ) 
 ) 
);