Lead/Lag Queries

[wyconian]

Hi

I have a table I'm trying to load in data manager into using a fact build.  The table has effective date and I need to calculate the corresponding end date as 1 second prior to the next effective date.

I can do this using lag/lead functions in the data source but is there anyway of doing this using DM functionality.

I'm using an oracle 10g database.

Any help with this is much appreciated.

Thanks

Simon

[MFGF]

Hi Simon,

One approach I can suggest is that you use a variable to store the ID of the current row in the build, then use a derived dimension to pull this value back in as the next row is processed and use it to locate the original row in a lookup (which will need to be coded beforehand to read in the ID and Effective Date of the same table).

Post back if you need more detail...

MF.

[wyconian]

Thanks that was the kind of thing I was thinking off.  Do you have any idea if this would be more efficient than forcing it back to the database in the data source query?

[MFGF]

I'd imagine so.  It would be difficult to code an efficient query to set the end date of the current row as the start date of the next row.  Depending on the volumes of data, it will be both easier and more efficient to use a lookup, allowing the processing to be done in memory.

MF.

[wyconian]

MF as always thanks a lot for your help

Simon

[MFGF]

No problem.  If you have any questions or problems setting the build up, give us a shout!

MF.