Reading and writing historic records

How can one, as part of a plan, write out (using the Jdbc Writer component) the current state of a record in a RDM table and the state of the same record as it was 30 days prior?

Regards baz

Hi @Arijit_Basu_Roy ,

I can think of 2 ways to achieve this, and depends on how you need the data to write to your jdbc writer.

  1. Run component twice and pass the current timestamp on the first run, and 30 days prior on the second run.
    This is assuming you are calling the component with possibly a .ewf workflow. Define the 2 timestamp variables in the workflow. Call the component twice, each time with each timestamp you have defined. In the component, parameterise the timestamp that feeds into the RDM Extended reader.
    Also, that you are happy with both sets of results as separate records in the same table, as the end result will have the records appended into the target table.

  2. In the same plan, make 2 RDM Extended reader steps.
    You can duplicate the RDM Extended reader step, and feed a different timestamp to each of them. In the Random Record Generator that feeds into the RDM Extended reader, I’d do something like 1 variable with current timestamp and the other with current timestamp - 30 days, and configure the reader steps to read 1 of each.
    Then use union or union same to put the 2 streams of results back into the same data flow and then use a single jdbc writer step to write to target table.
    This allows you to do any additional transformations you want before writing it as well (e.g. join those 2 streams together). It depends on what the structure is for your target table.

These are the most straight forward ways I can think of, you can possibly achieve this also by using the incremental export, do from beginning of time until “now”, and then do some clever filters. But those I think are more complex than the above 2.

Hope this helps? Let us know how it goes!

May

Hi May,
I used option two with a left Join to merge the current data (left side) with the value of an attribute from the previous quarter. The current and previous values needed to be output to the same row e.g.

Master key, Product Name, Current Qtr Price, Previous Quarter Price

Many thanks for your help…
baz