Adding Values for "Missing" Dates

[Former Member]

I've got an XML recordset with non-consecutive months and cooresponding data values for those months. I need to create a recordset with consecutive months, the values for the months to be added will be taken from the most recent month for while I have a data value, like so:

Original Table New Table

Date Value Date Value

09/01/2005 1735 09/01/2005 1735

10/01/2005 1735

11/01/2005 1735

12/01/2005 1735

01/01/2006 1760 01/01/2006 1760

02/01/2006 1829 02/01/2006 1829

03/01/2006 1829

04/01/2006 1829

05/01/2006 1856 05/01/2006 1856

06/01/2006 1856

07/01/2006 1856

Any ideas how to do this?

David

[jcgood25 · Accepted solution]

Have you looked at the Time Interpolator action?

[Former Member · Accepted solution]

I looked at it, Jeremy, but it seemed like it handled multiple data sets in the same result set (whereas I have one "incomplete" data set) and it had minimum configuration available. I went ahead and tried it with my data set but the output was identical to the input.

[jcgood25 · Accepted solution]

I guess I am a little puzzled with the way the forum posted the data, can you post the actual xml results of your source document and what you want as your output?

Also, what is the destination of this dataset? If this data is meant for an applet, depending upon the type, etc. you may not need to fill in the gaps.

[Former Member · Accepted solution]

Yeah - I should have previewed the post before I posted it. As for the destination - I'm going to run it through a Joiner action to merge it with a data set that has all dates for the last two years (my time frame of interest). I thought about using a Full Outer join then filling in the data that was missing at that point but that didn't seem any easier.

Here's the XML:

Original:

<?xml version="1.0" encoding="UTF-8"?><Rowsets DateCreated="2007-10-08T17:05:18" EndDate="2007-10-08T17:05:18" StartDate="2007-10-08T16:05:18" Version="11.5.3"><Rowset><Columns><Column Description="BDate" MaxRange="1" MinRange="0" Name="BDate" SQLDataType="93" SourceColumn="BDate"/><Column Description="Benchmark" MaxRange="1" MinRange="0" Name="Benchmark" SQLDataType="8" SourceColumn="Benchmark"/></Columns><Row><BDate>2005-09-01T00:00:00</BDate><Benchmark>1735</Benchmark></Row><Row><BDate>2006-01-01T00:00:00</BDate><Benchmark>1760</Benchmark></Row><Row><BDate>2006-02-01T00:00:00</BDate><Benchmark>1829</Benchmark></Row><Row><BDate>2006-05-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row></Rowset></Rowsets>

Final:

<?xml version="1.0" encoding="UTF-8"?><Rowsets DateCreated="2007-10-08T17:15:45" EndDate="2007-10-08T17:15:45" StartDate="2007-10-08T16:15:45" Version="11.5.3"><Rowset><Columns><Column Description="BDate" MaxRange="1" MinRange="0" Name="BDate" SQLDataType="93" SourceColumn="BDate"/><Column Description="Benchmark" MaxRange="1" MinRange="0" Name="Benchmark" SQLDataType="8" SourceColumn="Benchmark"/></Columns><Row><BDate>2005-09-01T00:00:00</BDate><Benchmark>1735</Benchmark></Row><Row><BDate>2005-10-01T00:00:00</BDate><Benchmark>1735</Benchmark></Row><Row><BDate>2005-11-01T00:00:00</BDate><Benchmark>1735</Benchmark></Row><Row><BDate>2005-12-01T00:00:00</BDate><Benchmark>1735</Benchmark></Row><Row><BDate>2006-01-01T00:00:00</BDate><Benchmark>1760</Benchmark></Row><Row><BDate>2006-02-01T00:00:00</BDate><Benchmark>1829</Benchmark></Row><Row><BDate>2006-03-01T00:00:00</BDate><Benchmark>1829</Benchmark></Row><Row><BDate>2006-04-01T00:00:00</BDate><Benchmark>1829</Benchmark></Row><Row><BDate>2006-05-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2006-06-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2006-07-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2006-08-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2006-09-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2006-10-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2006-11-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2006-12-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-01-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-02-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-03-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-04-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-05-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-06-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-07-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-08-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-09-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-10-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-11-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2007-12-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2008-01-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2008-02-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2008-03-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2008-04-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2008-05-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2008-06-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2008-07-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row><Row><BDate>2008-08-01T00:00:00</BDate><Benchmark>1856</Benchmark></Row></Rowset></Rowsets>

[jcgood25 · Accepted solution]

The TimeInterpolator wants two or more Rowset in the same Rowsets, which would typically look like a HistoryEvent query with more than one tag. Aside from the fact that the values would have different column names reflecting the requested tagnames, Rowset[1] would have varying dates/values, and row quantities from Rowset[2].

If you already have a dataset, query or otherwise that contains the master set of monthly dates, just append its Rowset to your Original set of data and you will have the 2 Rowset shape of data it wants. The block will then time interpolate the two datasets together filling in the blanks with a flat projection from the previous known value. If the first month in your earliest range doesn't exist in the other it will start with NA since there is no value to project.

If you can easily append something like this to what you called 'Original' in your earlier post as the second Rowset, then the Time Interpolator will create a BDate/Benchmark/DummyValue dataset that you can use the Column Stripper to remove the DummyValue column (with NA's in it) if needed:

<Rowset>

<Columns>

<Column Description="BDate" MaxRange="1" MinRange="0" Name="BDate" SQLDataType="93" SourceColumn="BDate"/>

<Column Description="DummyValue" MaxRange="1" MinRange="0" Name="DummyValue" SQLDataType="8" SourceColumn="DummyValue"/>

</Columns>

<Row>

<BDate>2005-09-01T00:00:00</BDate>

<DummyValue>NA</DummyValue>

</Row>

<Row>

<BDate>2005-10-01T00:00:00</BDate>

<DummyValue>NA</DummyValue>

</Row>

<Row>

<BDate>2005-11-01T00:00:00</BDate>

<DummyValue>NA</DummyValue>

</Row>

<Row>

<BDate>2005-12-01T00:00:00</BDate>

<DummyValue>NA</DummyValue>

</Row>

<Row>

<BDate>2006-01-01T00:00:00</BDate>

<DummyValue>NA</DummyValue>

</Row>

................

<Row>

<BDate>2008-08-01T00:00:00</BDate>

<DummyValue>NA</DummyValue>

</Row>

</Rowset>

Regards,

Jeremy

[Former Member · Accepted solution]

I can create a dataset with a master set of months (though since users will be adding values to the "original" set as times go by, I would need to create a master set with dates pushed out to, say, 2020.) Sounds doable.

One last question. How do I append the master set to the original set? Both will be created via SQL queries.

David

[jcgood25 · Accepted solution]

The Union action block will take the two Rowsets/Rowset from your separate SQL query blocks and turn it into Rowsets/Rowset/Rowset - which is what the Time Interpolator is expecting.

Regards,

Jeremy

[Former Member · Accepted solution]

Jeremy - can I award you fifty points on that one? Worked just slick! I'll have need for this on other projects - it'll come in very handy. Thanks!

[jcgood25 · Accepted solution]

Always glad to help out - especially when the SDN profile information is not anonymous.

You're lucky I worked you through this one, especially since you gave Sam my 10 points on an earlier one...

A few 'Very Helpful Answers' on the earlier posts in this thread is allowed

[Former Member · Accepted solution]

That was that Transaction properties in a calculated column conundrum. You're right - I shouldn't have penalized you for me spelling the property name wrong. I guess it was Sam pointing me to one of the lab examples that got my priorities all out of whack. I'll give you a few 'very helpful answers' on this one.

Thanks again, Jeremy - it really does work pretty slick!