Date Population

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
cherry
Participant
Posts: 108
Joined: Sun Jul 10, 2005 1:35 am

Date Population

Post by cherry »

Hi All,

Good Day..,

I have the following requirement.
From the source I would get only the START DATE and I need to populate END DATE in the Output file.

Greatly apprciated if anyone has solution.


INPUT DATA
----------
ID NBR SEQNO START DATE
7856 01 20100905 10:12:45
7856 01 20100905 10:16:14
7856 01 20100905 10:32:26


Scenario:
---------

1) If my source have only one record then my end date in the output is current time stamp.
2) If my source have more than one record, say it has 3 records. Then the END DATE for the first record is the
START DATE of the second record and the END DATE of the second record is START DATE of the third record and the
END DATE for the 3 record is CurrentTimeStamp(). The below is how the OUTPUT data looks like.

OUTPUT DATA
-----------
ID NBR SEQNO START DATE END DATE
7856 01 20100905 10:12:45 20100905 10:16:14
7856 01 20100905 10:16:14 20100905 10:32:26
7856 01 20100905 10:32:26 CurrentTimeStamp()

MANY THANKS
~~~Cherry
anbu
Premium Member
Premium Member
Posts: 596
Joined: Sat Feb 18, 2006 2:25 am
Location: india

Post by anbu »

Use transformer stage and have two links. One link with all the records and for other link remove the first record.

Create a dummy key in both these links and assign one to it.

Use these links as input to lookup stage on dummy key with link having all rows as main and other as reference.

The row which has date column from reference is null, assing current timestamp.

Run this job with one node configuration file
You are the creator of your destiny - Swami Vivekananda
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You may find a Join stage better than a Lookup stage in this design.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
cherry
Participant
Posts: 108
Joined: Sun Jul 10, 2005 1:35 am

Post by cherry »

Thanks anbu for th esolution. Let me try and get back to you.
Post Reply