View previous topic :: View next topic
|
Author |
Message |
chandracdac
New User
Joined: 15 Jun 2007 Posts: 92 Location: bangalore
|
|
|
|
Hi All,
i want to apply my eliminate duplicate logic(sum fields=None or sortxsum using ICETOOL) on certain records from input and remaining records should be copied to out put as is.
something like this
input is
aaa 123 a1 testing1
aaa 123 a1 testing1
aaa 124 b1 testing1
aaa 124 b1 testing1
output should be
aaa 123 a1 testing1
aaa 124 b1 testing1
aaa 124 b1 testing1
so it is basically when field has value a1 i want to eliminate duplicates and at the same time i want all other records in my output file. i have tried select statement using CTL1 but i am missing something.
this is what i have tried
SELECT FROM(inputfile ) TO(outputfile)-
ON(01,03,CH) ON(04,03,CH) ON(09,08,CH)-
FIRST DISCARD(SORTXSUM)-
USING(CTL1)
and my CTL1 is
INCLUDE COND=(07,2,CH,EQ,C'a1')
i know this will select records with only a1 but i tried all other options too |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Can "a1" appear with other keys as well? If so, do you want to keep the first of each key, or only the first "a1"? |
|
Back to top |
|
|
chandracdac
New User
Joined: 15 Jun 2007 Posts: 92 Location: bangalore
|
|
|
|
Bill, yes. a1 might appear with other keys also and i want to keep the first of each key |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
WHEN=GROUP with KEYBEGIN and PUSH a SEQ (make sure it is large enough to accomodate the maximum number of records for a key) to temporarily extend your records, and use OUTFIL OMIT= to discard any record with "a1" and SEQ-field greater than one. Use BUILD to drop off them temporary extension. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
And the assumption is that the input is already sorted on the key. |
|
Back to top |
|
|
chandracdac
New User
Joined: 15 Jun 2007 Posts: 92 Location: bangalore
|
|
|
|
No input is not sorted on the key.
@Bill could you give me the sort card ? |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
If the input is not in key order, why did you show your sample data to be in key order?
What order do you want the output to be in? Do you have a preference for which 'a1' is retained for a particular key, given that they may be scattered across your input?
There are loads of examples here (and elsewhere). It is not rocket surgery once you have the outline. Of course, you've just changed the outline. |
|
Back to top |
|
|
chandracdac
New User
Joined: 15 Jun 2007 Posts: 92 Location: bangalore
|
|
|
|
ok i will do my search for examples.
Thank you for your tips |
|
Back to top |
|
|
chandracdac
New User
Joined: 15 Jun 2007 Posts: 92 Location: bangalore
|
|
|
|
Ok so this is what i have done finally and i got my desired result:
SELECT FROM(inputfile ) TO(outputfile1)-
ON(01,03,CH) ON(04,03,CH) ON(09,08,CH)-
FIRST DISCARD(SORTXSUM)-
USING(CTL1)
COPY FROM(inputfile ) TO(outputfile2)-
USING(CTL2)
and my CTL1 is
INCLUDE COND=(07,2,CH,EQ,C'a1')
and my CTL2 is
INCLUDE COND=(07,2,CH,NE,C'a1')
i got my result in two different files outputfile1 and outputfile2 and i am ok with that. |
|
Back to top |
|
|
|