Rearrange the records based on all matched numbers

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
ushasribandaru
Participant
Posts: 15
Joined: Fri Nov 21, 2008 12:41 am

Rearrange the records based on all matched numbers

Post by ushasribandaru »

Hi,

I have recrds some thing like below

AGD U20081120AGDCPF Consolidated Unprocessed Payment Report AGD
Acc_No, CCY, Value_Date, Total, PROD
706155031,EUR,081124,1752.96,FTI
706155031,USD,081124,1752.96,FTI
38365029,SGD,081124,700,FTI
38365029,GBP,081124,381.88,FTI
706155078,EUR,081124,1752.96,FTI
706155031,SGD,081124,1752.96,DDE
706155031,USD,081124,1752.96,DDE
38365029,SGD,081124,700,DDE
706155078,EUR,081124,1752.96,DDE


And i want to arrange them in the way that all the records in order having same account numbers.

Example like below(Required output)

AGD U20081120AGDCPF Consolidated Unprocessed Payment Report AGD
Acc_No, CCY, Value_Date, Total, PROD
706155031,EUR,081124,1752.96,FTI
706155031,SGD,081124,1752.96,DDE
706155031,USD,081124,1752.96,FTI
706155031,SGD,081124,1752.96,DDE
38365029,SGD,081124,700,FTI
38365029,SGD,081124,700,DDE
38365029,GBP,081124,381.88,FTI
706155078,EUR,081124,1752.96,FTI
706155078,EUR,081124,1752.96,DDE

I tried with extracting group of records if the ( accno for PROD(FTI) = accno for PROD(DDE))

=F_ShuffleRec(EXTRACT(Record_FTI Group:Output,AcctNum:Record_FTI Group:Output=
AcctNum:Record_DDE Group:Output ),EXTRACT(Record_DDE Group:Output,AcctNum:Record_DDE Group:Output=AcctNum:Record_FTI Group:Output ))


I am getting some repeated records like below:

AGD U20081120AGDCPF Consolidated Unprocessed Payment Report AGD
Acc_No, CCY, Value_Date, Total, PROD
706155031,EUR,081124,1752.96,FTI
706155031,SGD,081124,1752.96,DDE
706155031,USD,081124,1752.96,FTI
706155031,SGD,081124,1752.96,DDE
706155031,EUR,081124,1752.96,FTI
706155031,USD,081124,1752.96,DDE
706155031,USD,081124,1752.96,FTI
706155031,USD,081124,1752.96,DDE
38365029,SGD,081124,700,FTI
38365029,SGD,081124,700,DDE
38365029,GBP,081124,381.88,FTI
38365029,SGD,081124,700,DDE
706155078,EUR,081124,1752.96,FTI
706155078,EUR,081124,1752.96,DDE


Can any one help on this,

Thanks,
Usha.
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Wouldn't you just... sort... them? :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

I would.

Or, if I needed probabilistic rather than deterministic matching, I'd use QualityStage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
ushasribandaru
Participant
Posts: 15
Joined: Fri Nov 21, 2008 12:41 am

Post by ushasribandaru »

[quote="chulett"]Wouldn't you just... sort... them? :?[/quote]


Even it's not working in my scenario,insted the repeted records getting increased
Top
ushasribandaru
Participant
Posts: 15
Joined: Fri Nov 21, 2008 12:41 am

Post by ushasribandaru »

[quote="ray.wurlod"]I would.

Or, if I needed probabilistic rather than deterministic matching, I'd use QualityStage. ...[/quote]

I am not aware of this QualityStage, can u help me to explain me in detail

Thank You
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

What's not working? Post your job design, where it seems that just a Sort stage would be needed with no stage variable shenenigans that you seem to be using. :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

ushasribandaru wrote:I am not aware of this QualityStage, can u help me to explain me in detail
U probably could but you really should ask Ray. And because you aren't aware of it, you probably don't have it so it will be somewhat... academic.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

Search worked for me, were you not able to search for some reason?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
kandyshandy
Participant
Posts: 597
Joined: Fri Apr 29, 2005 6:19 am
Location: Singapore

Post by kandyshandy »

Where from you are getting source data? From a table or a file?

If it is from a table, add a ORDER BY ACC NO in your SELECT SQL.
If it is from a sequential file, tell us your job design... i.e what is your source stage, target stage, processing stages in between etc.

Based on the forum index, i believe that your job is a server job. Please let us know if it is otherwise.
Kandy
_________________
Try and Try again…You will succeed atlast!!
Top
ushasribandaru
Participant
Posts: 15
Joined: Fri Nov 21, 2008 12:41 am

Post by ushasribandaru »

[quote="kandyshandy"]Where from you are getting source data? From a table or a file?

If it is from a table, add a ORDER BY ACC NO in your SELECT SQL.
If it is from a sequential file, tell us your job design... i.e what is your source stage, target stage, processing stages in between etc.

Based on the forum index, i believe that your job is a server job. Please let us know if it is otherwise.[/quote]


Thank You :)
Top
ushasribandaru
Participant
Posts: 15
Joined: Fri Nov 21, 2008 12:41 am

Post by ushasribandaru »

[quote="ray.wurlod"][url=http://www-01.ibm.com/software/data/inf ... litystage/]Search[/url] worked for me, were you not able to search for some reason? ...[/quote]

Thank you very much,I got it :)
Top
Post Reply

Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”