0

Can I compare these two rows using DataStage then update if the Status field changing?

    ID | desc | ... | ... | Status | Start_Date | End_Date
------------------------------------------------------------
123456 | ...  | ... | ... |   Y    | 12/31/2014 | 21/31/9999
123456 | ...  | ... | ... |   N    | 12/31/2014 | current_date

At beginning I only have this row

    ID | desc | ... | ... | Status | Start_Date | End_Date
------------------------------------------------------------
123456 | ...  | ... | ... |   Y    | 12/31/2014 | 21/31/9999

Then... I want to compare with this row

   ID | desc | ... | ... | Status | Start_Date | End_Date
------------------------------------------------------------
123456| ...  | ... | ... |   N    | 12/31/2014 | current_date

If the Status field changing from Y to N, I want to update the End_Date with current_date. If the Status field changing from N to Y, I want to update the Start_Date with current_date and End_Date with 12/31/9999 again. Is it possible to do this in DataStage? I tried using Switch Processing, but stuck when I want to Update in Netezza Connector.

colintobing
  • 167
  • 2
  • 14

1 Answers1

1

From table only extract records which are having End_Date = 12/31/9999

Then with look up stage compare on ID and Status with the new records, if match then sotre in to a update file and the one which do not match you can directly insert in to table.

And in the update file you must keep the both new records and the data which you fetch from table.

Now sort the records on ID and Start_Date in a transformer satge , and in a stage variable have if else condition like if Status is N then end the date of previous date and populate End_Date = 12/31/9999 of current date.

Hope this helps.

Abhis
  • 585
  • 9
  • 25
  • Thanks Abhis for answering my question. It's about storing slow moving dimension using datastage. I tried to use SCD process but still failed. I miss with the Surrogate key, Business key, Type1, Type2, etc... – colintobing Jul 21 '15 at 00:14