SCD type 2 in server jobs in DS 7.1....Need help

[johnsk]

Hi,

I need help to implement like SCD type 2 in Server jobs DS 7.1,

n i have two columns in source.... pol no. and seq no., but "seq no." is not populating properly current in source...so need to correct it...

Source data example,

First run,

pol no. | seq no. |
171 | 1 |
171 | 4 |
171 | 5 |
172 | 1 |
172 | 3 |

Target data should be like below example,

After First run,

pol no. | seq no. |
171 | 1 |
171 | 2 |
171 | 3 |
172 | 1 |
172 | 2 |


Source data example,

2nd run,

pol no. | seq no. |
171 | 1 |
171 | 4 |
173 | 3 |
172 | 2 |
174 | 3 | ....n so on

Final Target data should be like below example,

pol no. | seq no. |
171 | 1 |
171 | 2 | ....earlier 1st run target data
171 | 3 |
172 | 1 |
172 | 2 |

171 | 4 |
171 | 5 |
173 | 1 | .....+ 2nd run target data
172 | 3 |
174 | 1 | ....n so on.....

As in need to correct seq no. of current data and going further it should also generate new seq no. (if pol no. exist in table then max(seq) + 1 and if it doesnot exist in table then should start from 1...for all src records to target table)
Please let me know best method to do this.....

[chulett]

Sort by pol no and original seq no, then use stage variables to assign the new seq nos: new pol = 1, not new pol = previous seq + 1.

Code: Select all

Stage variable       Derivation                              Initial value
svNewPol             Link.PolNo <> svOldPol                  @FALSE
svSeqNo              If svNewPol Then 1 Else svSeqNo + 1     0
svOldPol             Link.PolNo                              0

[suresh.angadi]

Hi Jhon,

user CRC32() function to identify the change in the source and then you can use any of the scd-type 2 that is timestamp or versioning.

Regards
Suresh

Hi,

I need help to implement like SCD type 2 in Server jobs DS 7.1,

n i have two columns in source.... pol no. and seq no., but "seq no." is not populating properly current in source...so need to correct it...

Source data example,

First run,

pol no. | seq no. |
171 | 1 |
171 | 4 |
171 | 5 |
172 | 1 |
172 | 3 |

Target data should be like below example,

After First run,

pol no. | seq no. |
171 | 1 |
171 | 2 |
171 | 3 |
172 | 1 |
172 | 2 |


Source data example,

2nd run,

pol no. | seq no. |
171 | 1 |
171 | 4 |
173 | 3 |
172 | 2 |
174 | 3 | ....n so on

Final Target data should be like below example,

pol no. | seq no. |
171 | 1 |
171 | 2 | ....earlier 1st run target data
171 | 3 |
172 | 1 |
172 | 2 |

171 | 4 |
171 | 5 |
173 | 1 | .....+ 2nd run target data
172 | 3 |
174 | 1 | ....n so on.....

As in need to correct seq no. of current data and going further it should also generate new seq no. (if pol no. exist in table then max(seq) + 1 and if it doesnot exist in table then should start from 1...for all src records to target table)
Please let me know best method to do this.....

[johnsk]

I have resolved this longtime back but forgot to mention in DSXchange, i used some workaround my self and resolved it.

I created to 2 jobs,
1. 1st job loads data from table 'A' (Ora stage) with max seq no. of each pol no. in hash file. (First run table would be empy)
2. 2nd job uses SRc file does a lookup with hashfile (using transformer stage) and loads to target table 'A' (Ora Stage) and TFM stage i used stage var with one function which will compare current pol no. with prev pol no. to check both are same or not (func o/p gives 0 (not same) or 1 (same)...currently i don't remember the func name...(something previouscompare - need to check)...so using this value with one if n else condition in stage var...(prev value and current value +1)...i solved this issue easily and perfectly!!! Thanks anyways!!! Cheers!!!