Page 1 of 1

DFSORT include cond on first record group

PostPosted: Fri Dec 16, 2016 11:41 pm
by wolfi57
hi,
i have some trouble to do some sorting data set
my data set is structured like :
year 9(04)
typ 0 or 1 or 2 pic 9(02)
unique key for group record
group of record on (key,typ)
record contain one particular fields A(mnt signed packed decimal s9(11)V9(02) comp 3) which i want test value but only if first record typ is 0 or 1



input data set
YYYY 0 key1 A
YYYY 0 key2 A
YYYY 1 key3 A
YYYY 2 key3 A
YYYY 0 key4 A
YYYY 2 key4 A
YYYY 1 key5 A
YYYY 2 Key6 A
YYYY 0 Key7 A
YYYY 0 key8 A
YYYY 0 key9 A
YYYY 2 key9 A



this sort is actually obtain by a sort fields

But how grouping and include only group of record satified some condition on first record typ
I want include only group record with the same key and which satisfied test value of A only on record typ 0 or 1 , record typ 0 and 1 are mutualy exclusive
for example:
I retain group
YYYY 0 key4 A
YYYY 2 key4 A
if test value A of record 0 is satisfied

I exclude record
YYYY 2 key6 A because no record typ 0 or 1

I exclude group
YYYY 0 key9 A
YYYY 2 key9 A

and record
YYYY 0 key2 A
for the same reason value A of record typ 0 don't satisfied condition


OUTPUT

the output must rearrange column / line ( excel need unpack format)
to obtain
YYYY * selected field of record typ 0 or 1 * eventually selected field of record type 2 if satisfied group * key *

i don't know how obtain this result and if it's possible without lost performance ( it's a big data set)

Re: DFSORT include cond on first record group

PostPosted: Mon Dec 19, 2016 8:23 pm
by wolfi57
well it seems not clear
so let me explain
I have a file lrecl 50 with this structure

YYYY 00000000001 0 MNT11
YYYY 00000000001 2 MNT12
YYYY 00000000002 2 MNT21
YYYY 00000000003 1 MNT31
YYYY 00000000004 1 MNT41
YYYY 00000000004 2 MNT42
YYYY 00000000005 0 MNT51
YYYY 00000000006 0 MNT61
YYYY 00000000007 2 MNT71
YYYY 00000000008 2 MNT81
YYYY 00000000009 0 MNT91
YYYY 00000000009 1 MNT92
 

pos 1-4 YYYY year
pos 1-11 key
pos 13-13 typ cod
pos 14-20 amount  PIC S9(11)V99  COMP-3. for simplify let say MNTXX
 


the file is not sorting at DFSORT entry

I want to do in the same step of dfsort ( not icetool or syncsort , when=group and keybegin allowed)

1) sorting file by year then key then typ cod

2) applying rule to include group of record

for example these record belong to same group
same key
00000000001 0 MNT11
00000000001 6 MNT12
there are not necessary 2 records ( but 2 record is the max for the same key)

rule are :
retain group of record only if amount of typ record 0 or 1 ( mutually exclusive ) satisfy a condition let's say equal a certain amount 1000
IF (typ cod =00 or 5) and ( MNT = 1000) retain the record group

3) 2 output like
first output
00000000001 * MNT11,00 *      0,00     * MNT12,00  
00000000002 *    0,00      *      0,00     * MNT21,00
00000000003 *    0,00      *  MNT31,00 *     0,00  
00000000004 *    0,00      *  MNT41,00 * MNT42,00  
00000000005 * MNT51,00 *       0,00    *     0,00        
00000000006 * MNT61,00 *       0,00    *     0,00
00000000007 *     0,00     *       0,00    * MNT71,00
00000000008 *     0,00     *       0,00    * MNT81,00
00000000009 * MNT91,00 *  MNT92,00 *    0,00    


second output summarise by year
sum YYYYY    *  sum1,00  *  sum2,00   *   sum3,00             <-------- trailer sum each column

Re: DFSORT include cond on first record group

PostPosted: Mon Dec 19, 2016 8:25 pm
by Aki88
Hello,

From what I understood of the requirement, here goes; for simplicity's sake have added the last character on column 13 of your data, so the input looks something as:


YYYY 0 KEY1 B
YYYY 0 KEY2 B
YYYY 1 KEY3 B
YYYY 2 KEY3 B
YYYY 0 KEY4 A
YYYY 2 KEY4 A
YYYY 1 KEY5 C
YYYY 2 KEY6 C
YYYY 0 KEY7 C
YYYY 0 KEY8 C
YYYY 0 KEY9 C
YYYY 2 KEY9 C
 


Notice that I've forcefully tried to satify the condition for 'KEY4' as the conditions weren't really clear.

Code:

*                                                                      
* GROUP THE DATA ON THE BASIS OF THE 'KEY' AND THE LAST IDENTIFIER OF  
* THE FIRST RECORD IN THE GROUP. IT IS ASSUMED THAT THE INPUT DATASET IS
* SORTED ON THE BASIS OF THE 'KEY'; IF NOT - THEN THE USER NEEDS TO    
* GET THIS DONE PRIOR USING THIS SORT CODE.                            
*                                                                      
 INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(8,4),PUSH=(60:6,8))                
*                                                                      
* NOW THAT THE DATA HAS BEEN GROUPED, LET US APPLY THE CONDITIONS, THIS
* CAN BE ACHIEVED USING IFTHEN AGAIN, BUT I PREFER AN OMIT HERE,        
* PROGRAMMER'S CHOICE. THIS OMIT CONDITION IS TESTING THE KEY VALUES    
* FOR EACH RECORD THAT IS FALLING UNDER A GROUP.                        
*                                                                      
 OUTFIL REMOVECC,                                                      
        INCLUDE=(((60,1,CH,EQ,C'0'),OR,                                
                   (60,1,CH,EQ,C'1')),AND,                              
                 (67,1,CH,EQ,C'A')),                                    
*                                                                      
* BUILD CODED HERE WILL ALLOW THE USER TO FORMAT THE SELECTED RECORDS  
* AS PER THEIR WISH.                                                    
*                                                                      
        BUILD=(1,59)                                                    
*                                                                      
* DATA HAS BEEN FILTERED AND BUILT AT THIS POINT, DUMP THE SAME INTO THE
* OUTPUT DATASET.                                                      
*                                                                      
 SORT FIELDS=COPY                                                      
 



wolfi57 wrote:the output must rearrange column / line ( excel need unpack format)
to obtain
YYYY * selected field of record typ 0 or 1 * eventually selected field of record type 2 if satisfied group * key *

i don't know how obtain this result and if it's possible without lost performance ( it's a big data set)


Please note that rearrangement can be taken care of once correct data filtering is completed. Also 'big' here means nothing in particular unless you typically specify 'how big' the 'big' really is.


Edit: Looks like TS's update and this post coincided.

Re: DFSORT include cond on first record group

PostPosted: Thu Dec 22, 2016 12:42 am
by wolfi57
If i have for example
1999 0 key4 A
2000 6 key4 A

Sort considere thèses 2 record belong to the same group or they must considere not in same group
In fact rupture Key is composed by YYYY keys, for simplification Fields are contigu but in fact they are not
YYYY ..... 0 keys4 ..... A
Apologize for confusing

Re: DFSORT include cond on first record group

PostPosted: Thu Dec 22, 2016 12:38 pm
by Aki88
Hello,

If you think about it, contiguous keys or not - data can be formatted, keys can be built. What matters more is knowing how you want to drive the process AND on what keys. Once you have that figured out, then extend the record temporarily using INREC, you can use 'IFTHEN/INIT' to apply this extension on all records, then GROUP the records on this extension, this will enable you to apply the conditional checks on every records of that GROUP.
Once you have this built, it is a simple INCLUDE/OMIT which again can be used either directly or via an 'OUTFIL IFTHEN' construct.

Lastly, coming to the point of SORT on year, once you build the prime-KEY using INREC, add the SORT FIELDS on this key- and now you have records SORTed on the order you're looking for. Apply the conditional logic on this data during output processing.

Hope this helps.

Re: DFSORT include cond on first record group

PostPosted: Thu Dec 22, 2016 1:25 pm
by wolfi57
I agree, but that's the point how can I group record on same keys and same date, with when group on beginkey for date , I don't know how to manage with your suggestion, to extend the problem how grouping record based on several keys like YYYY and keys
for example record with same keys but different YYYY are record from different group

Re: DFSORT include cond on first record group

PostPosted: Fri Dec 23, 2016 12:29 am
by wolfi57
wel now i have my selective record , i succeed to manage rearrange record see in desired output
by using inrec control and build depend on several ifthen


YYYY 00000000001 * MNT11,00 * 0,00 * MNT12,00
YYYY 00000000002 * 0,00 * 0,00 * MNT21,00
YYYY 00000000003 * 0,00 * MNT31,00 * 0,00
YYYY 00000000004 * 0,00 * MNT41,00 * MNT42,00
YYYY 00000000005 * MNT51,00 * 0,00 * 0,00
YYYY 00000000006 * MNT61,00 * 0,00 * 0,00
YYYY 00000000007 * 0,00 * 0,00 * MNT71,00
YYYY 00000000008 * 0,00 * 0,00 * MNT81,00

now i must produce some report with header
and trailer with total amount in each column by year or whatever keys

header
*****************************************************************************************
keys * mntA * MNTB * MNTC
*****************************************************************************************
YYYY1 * 123,123,123,99 * 123,123,123,99 * 123,123,123,99 *
******************************************************************************************
YYYY2 * 123,123,123,99 * 123,123,123,99 * 123,123,123,99 *