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
pravin1581
Premium Member
Posts: 497 Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:
Post
by pravin1581 » Thu Aug 30, 2007 3:28 am
Hi All,
There is a requirement . Suppose a file contains the following data :-
Code: Select all
C1 C2
A 1
A 5
A 10
A 5
B 2
B 1
C 51
The output file should have the following data :-
The output file should contain the concatenated non duplicate values.Please help me out.
Thanx in advance.
JoshGeorge
Participant
Posts: 612 Joined: Thu May 03, 2007 4:59 am
Location: Melbourne
Post
by JoshGeorge » Thu Aug 30, 2007 3:37 am
In the input sequential file stage filter option use (Hopefully you have MKS Toolkit or similar installed)
and then use pivot stage to get your results.
Akumar1
Participant
Posts: 48 Joined: Tue May 22, 2007 3:38 am
Location: bangalore
Contact:
Post
by Akumar1 » Thu Aug 30, 2007 4:17 am
since it is windows environment,i don't think that ((sort -u))
command can be used.because the option in enabled only in unix environment.
Regards,
Akumar1
JoshGeorge wrote: In the input sequential file stage filter option use (Hopefully you have MKS Toolkit or similar installed)
and then use pivot stage to get your results.
JoshGeorge
Participant
Posts: 612 Joined: Thu May 03, 2007 4:59 am
Location: Melbourne
Post
by JoshGeorge » Thu Aug 30, 2007 4:23 am
Pls. search the forum with key words 'MKS Toolkit' for clarity.
Replied inline
(Hopefully you have MKS Toolkit or similar installed)
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Thu Aug 30, 2007 5:08 am
Search the forum for vertical pivot - there are at least three ways to solve this in server jobs, depending on whether the number of rows per key is small or large, fixed or arbitrary. You example suggests small and arbitrary, but who knows if you've supplied the correct data?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sachin1
Participant
Posts: 325 Joined: Wed May 30, 2007 7:42 am
Location: india
Post
by sachin1 » Thu Aug 30, 2007 9:26 am
if your input file is a hash file with C1 and C2 as key then i think you get unique records like
C1 C2
A 1
A 5
A 10
B 2
B 1
C 51
Now follow below steps.
your job design will be like
H/C file(hash input file) ----> transformer---> hash(H/C) file(output)
1. Create 2 stage variables like APPEND_C2_VALUE and C1_VALUE.
2.In transformer give the value for C1_VALUE = trim(input_link.C1).
3. In transformer give the value for APPEND_C2_VALUE like below
if input_link.C1 = C1_VALUE
Then APPEND_C2_VALUE :',': input_link.C2
Else input_link.C2
4.in your final output hash file put two columns like A and B.
values for column A = input_link.C1 and value for column B = APPEND_C2_VALUE.
this works fine please check.