GROUP Records based on a KEY and select MIN



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

GROUP Records based on a KEY and select MIN

Postby anil4321 » Fri Jun 19, 2009 6:42 pm

I have a file(VB LRECL=71) with the following columns:

FIELD1= CHAR(6)
KEY1=CHAR(16)
TIMESTAMP=CHAR(26) DB2 Timestamp
KEY2=2 bytes Number
FIELD2=CHAR(16)

INPUT
----------
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-01.00.00.0000000011234567890123456
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-02.00.00.0000000011234567890123456
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-00.00.00.0000000021234567890123457
yyxxxxBBBBBBBBBBBBBBBB2009-01-01-00.00.00.0000000011234567890123456

Records are grouped by KEY1.
If there are records with KEY2 =1 for a group, select the records with KEY2=1 for which TIMESTAMP has the lowest value.
If there are records with KEY2 !=1 for a group, select the record for which TIMESTAMP has the lowest value.

There are two groups(Unique KEY1): AAAAAAAAAAAAAAAA and BBBBBBBBBBBBBBBB.
For group AAAAAAAAAAAAAAAA, we have two records with KEY2=1. So we select the record that has the lowest TIMESTAMP.
For group BBBBBBBBBBBBBBBB, we do not have records with KEY2=1. So we select the record that has the lowest TIMESTAMP.

Our output will have two records, one for each group.

OUTPUT
----------
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-01.00.00.0000000011234567890123456
yyxxxxBBBBBBBBBBBBBBBB2009-01-01-00.00.00.0000000011234567890123456

Can ICETOOL be used to produced this result?
anil4321
 
Posts: 11
Joined: Wed Nov 19, 2008 4:23 am
Has thanked: 0 time
Been thanked: 0 time

Re: GROUP Records based on a KEY and select MIN

Postby Frank Yaeger » Fri Jun 19, 2009 10:08 pm

Your description of the data does NOT match your example. Your description has lengths adding up to 66 bytes, but your example has records of 67 bytes:

INPUT
----------
FIELD1<-KEY1---------><timestamp--------------->?K2<-FIELD2------->
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-01.00.00.0000000011234567890123456
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-02.00.00.0000000011234567890123456
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-00.00.00.0000000021234567890123457
yyxxxxBBBBBBBBBBBBBBBB2009-01-01-00.00.00.0000000011234567890123456


It appears you have an extra 0 byte before KEY2, but I can't tell for sure.

For group BBBBBBBBBBBBBBBB, we do not have records with KEY2=1. So we select the record that has the lowest TIMESTAMP.


It appears to me that for this group, you do have a record with KEY2=1. But maybe that's because your example doesn't match your description.

Please show an accurate example of your input records and expected output records. Show all possible variations. Use bbcode tags around your examples.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: GROUP Records based on a KEY and select MIN

Postby anil4321 » Fri Jun 19, 2009 11:31 pm

Here is my INPUT and desired OUTPUT:

Column <Record> indicates the line number in the file and is not a data field.
INPUT
----------
FIELD1<-KEY1---------><TIMESTAMP---------------->K2<-FIELD2------->  Record
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-01.00.00.0000000011234567890123456     1
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-02.00.00.0000000011234567890123456     2
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-00.00.00.0000000021234567890123457     3
yyxxxxBBBBBBBBBBBBBBBB2009-01-01-00.00.00.0000000021234567890123456     4
yyxxxxCCCCCCCCCCCCCCCC2009-02-01-00.00.00.0000000021234567890123456     5
yyxxxxCCCCCCCCCCCCCCCC2009-01-01-00.00.00.0000000031234567890123456     6

OUTPUT
----------
FIELD1<-KEY1---------><TIMESTAMP---------------->K2<-FIELD2------->  Record
yyxxxxAAAAAAAAAAAAAAAA2009-01-01-01.00.00.0000000011234567890123456     1
yyxxxxBBBBBBBBBBBBBBBB2009-01-01-00.00.00.0000000021234567890123456     4
yyxxxxCCCCCCCCCCCCCCCC2009-01-01-00.00.00.0000000031234567890123456     6


Output contains only one record from each group(unique KEY1).

For the unique key KEY1=AAAAAAAAAAAAAAAA, there are two records with KEY2=01.
So selection will be made from these two records(Record 1 and 2) based on the value in TIMESTAMP column.
Since TIMESTAMP for record 1 is less then that of record 2, record 1 is selected from INPUT file(2009-01-01-01.00.00.0000000 < 2009-01-01-02.00.00.0000000).


There is only one record for KEY1=BBBBBBBBBBBBBBBB. So record 4 is selected from the INPUT file.

There are two records for KEY1=CCCCCCCCCCCCCCCC. None of these records have KEY2=01.
So selection will made based on the record with lowest TIMESTAMP. We select record 6 as it has the lowest TIMESTAMP.

I have tried to solve it in four steps as follows:

1. Split the INPUT file into two. FILE1 has records with KEY2=1 and FILE2 has records with KEY2!=1.
2. Sort FILE1 using KEY1 and TIMESTAMP with FIRSTDUP.
3. Sort FILE2 using KEY1 and TIMESTAMP with FIRSTDUP.
4. Sort FILE1 and FILE2 (concatenated file) using KEY1 and KEY2 and use FIRSTDUP to get the first record.

How can I resolve this in just one step?
anil4321
 
Posts: 11
Joined: Wed Nov 19, 2008 4:23 am
Has thanked: 0 time
Been thanked: 0 time

Re: GROUP Records based on a KEY and select MIN

Postby Frank Yaeger » Sat Jun 20, 2009 2:01 am

Here's a DFSORT/ICETOOL job that will do what you asked for in one pass:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN DD DSN=...  input file (VB/71)
//OUT DD DSN=...  output file (VB/71)
//TOOLIN   DD    *
SELECT FROM(IN) TO(OUT) ON(12,16,CH) FIRST USING(CTL1)
/*
//CTL1CNTL DD *
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,5:C'1',6:5)),
        IFTHEN=(WHEN=(55,2,CH,EQ,C'01'),OVERLAY=(5:C'0'))
  SORT FIELDS=(12,16,CH,A,5,1,CH,A,28,27,CH,A)
  OUTFIL FNAMES=OUT,BUILD=(1,4,5:6)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post