If values within 1 day, merge

[cbaqir]

Trying to clean up some data...

My requirement is that if the last reviewed date is within 1 day of the last reviewed date of the SAME PowerPlan Name, the data should be merged together.

For example, since the Last Reviewed datre for the Vasoactive Agents PowerPlan is within 1 day, I want both dates to show 11/20/2007 so the rows are merged. Not sure how to accomplish this.

[m_woolf]

I would do something like this in script:

TableTemp:

Load

     Whatever Fields,

     PowerPlanName,

     LastReviewed,

     if(PowerPlanName = Peek('PowerPlanName') and peek('LastReviewed')-LastReviewed<2,1,0) as Remove

from datasource

order by PowerPlanName,LastReviewed;

Table:

noconcatenate

load

     *

resident TableTemp

where Remove = 0;

drop table TableTemp;

[Or]

The above approach from m w should work (though it doesn't actually save any indication that lines have been merged - you may want to add an indicator for that), but I'd like to kinda understand something here: what do you expect to happen if you have THREE similar lines at one day intervals, e.g. one each for 11/20, 11/21, and 11/22? Are these supposed to be merged into one line? Should one pair be merged and the third left intact? If so, which pair should be merged? If your data can have this situation, you'll want to make sure you know what the expected outcome is and tailor the solution to that.

[cbaqir]

We have 2 domains - A and B so we should not have 3 separate rows.

[cbaqir]

I need to also add in the usage for both rows. Will this do that or remove it?

[cbaqir]

This doesn't seem to be working for me. See attached.

[m_woolf]

Try this:

TableTemp:

Load

*,

     if(DESCRIPTION = Peek('DESCRIPTION') and peek('LAST_REVIEW_DATE')-LAST_REVIEW_DATE<2,1,0) as Remove

Resident REVIEW

order by DESCRIPTION,LAST_REVIEW_DATE;

Drop table REVIEW;

REVIEW:

noconcatenate

load

     *

resident TableTemp

where Remove = 0;

drop table TableTemp;