DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
hitmanthesilentassasin
Participant



Joined: 13 Mar 2007
Posts: 150

Points: 1382

Post Posted: Wed Jul 31, 2013 7:53 pm Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
Hi,

I am generating a golden record based on the matched record. after the match process I replace the primary key in the qsmatchsetid. the matched record is treated as golden record. This works fine for the same set of records . However, when there is addition to the group this golden record seems to change which is causing an issue to maintain the history of the data. can any one suggest any method to fix this issue? I have already tried sort the data but the sort order is not relevant when a new record is introduced in it. can't think of anything yet.

Thanks for any inputs or ideas

Cheers
rjdickson
Participant



Joined: 16 Jun 2003
Posts: 378
Location: Chicago, USA
Points: 2531

Post Posted: Thu Aug 01, 2013 5:56 am Reply with quote    Back to top    

Hi,

Sorry, but I do not completely understand the question. Are you using the Survive stage? Can you describe the QualityStage job you have created and give data examples?

_________________
Regards,
Robert
Rate this response:  
Not yet rated
hitmanthesilentassasin
Participant



Joined: 13 Mar 2007
Posts: 150

Points: 1382

Post Posted: Thu Aug 01, 2013 6:13 am Reply with quote    Back to top    

Hi,

here is the sample input and output

First input
Code:
Firstname lastname phone primarykey
Jack          Smith    1234     123
Jack          Smith    1234      345 



after processing through match specification

Code:
Firstname lastname phone primarykey  QSDataId  QSMatchsetId
Jack          Smith    1234     123           1                  1
Jack          Smith    1234      345          2                  1


I am replacing the qsmatchsetid with primarykey to store in the db

Code:
Firstname lastname phone primarykey  QSMatchsetId
Jack          Smith    1234     123                           123
Jack          Smith    1234      345                           123


this remains same as long as the group remains same. but when a new record is introduced the qsmatchsetid will change but since, previously it was generated as 123 it should still remain 123 also below is the scenario depicting it.

Second input
Code:
Firstname lastname phone primarykey
Jack          Smith    1234     123
Jack          Smith    1234      345 
Jack          Smith    1234      000 


expected output this time

Code:
Firstname lastname phone primarykey  QSMatchsetId
Jack          Smith    1234     123                           123
Jack          Smith    1234      345                           123
Jack          Smith    1234      000                           123


my question is, Is it possible to keep generating the same qsmatchsetid key regardless of addition of a record or not?
Rate this response:  
Not yet rated
rjdickson
Participant



Joined: 16 Jun 2003
Posts: 378
Location: Chicago, USA
Points: 2531

Post Posted: Thu Aug 01, 2013 2:59 pm Reply with quote    Back to top    

Hi,

No you can not reliably control QSMatchSetID. It can be different for each run.

I would not recommend re-purposing QSMatchSetID - call it 'NewPrimaryKey' or something like that (to avoid confusion).

After the first match, you would have:
Code:
Firstname lastname phone primarykey  QSDataId  QSMatchsetId NewPrimaryKey
Jack      Smith    1234  123         1         1            123
Jack      Smith    1234  345         2         1            123


The NewPrimaryKey would be populated by you after the match processing completed.

Now after the second match, your output may look like this:

Code:
Firstname lastname phone primarykey  QSDataId  QSMatchsetId QSMatchType NewPrimaryKey
Jack      Smith    1234  567         100       100          MP         
Jack      Smith    1234  123         200       100          DA          123
Jack      Smith    1234  345         300       100          DA          123


Note that in my example, the new record actually became the master - an assumption for this illustration; it could be anywhere in the group.

You can now process this output to populate the missing 'NewPrimaryKey'

_________________
Regards,
Robert
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54479
Location: Sydney, Australia
Points: 295436

Post Posted: Thu Aug 01, 2013 6:08 pm Reply with quote    Back to top    

You might even countenance using a Survivorship rule to accomplish that, such as "most frequently occurring non-blank".

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours