Maching with IF clauses



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

Maching with IF clauses

Postby pulcinella » Wed Feb 22, 2012 4:08 pm

Hello

I have two input files, one (input1) of 570 positions and the other (input2) 30. Both files are sorted ascending by the same key. The first file has no duplicates. The second has duplicates which I will have to do a loop until you change the second file key updating indicators.

input file 1 (570 positions)
------------

field1-a pic x(14)
key1 pic x(6)
field1-b pic x(550)

input file 2 (30 positions)
------------

key2 pic x(6)
amount2-a pic x(6)
amount2-b pic x(6)
amount2-c pic x(6)
amount2-d pic x(6)

I Need to obtain an output file with the content of file 1 and 6 indicators (Y / N), depending on the fields amount2-a, amount2-b,-c amount2, amount2-d.

output file (576 positions)
-----------

field1-a pic x(14)
key1 pic x(6)
field1-b pic x(550)
ind1-a pic x(1)
ind1-b pic x(1)
ind1-c pic x(1)
ind1-d pic x(1)
ind1-e pic x(1)
ind1-f pic x(1)

By default, the 6 indicators (ind1-a, ind1-b, ind1-c, ind1-d, ind1-e, ind1-f) come with "N"

If the field amount2-b = '110065' or '110070' or '110078' write 'Y' in the ind1-a
If the field amount2-a = '300158' write 'Y' in the ind1-b
If the field amount2-b = '100034' or '100035' or '100037 'or '100038' write 'Y' in the ind1-c
If the field amount2-b = '100014' write 'Y' in the ind1-d
If the field amount2-b = '130177' write 'Y' in the ind1-e
If the field amount2-b = '110331' or '110334' or '110335 'or '110336' or '110337' or '110339' write 'Y' in the ind1-f

Failure to meet any of these conditions, the indicators will come with "N".

As you can see the field amount2-c and amount2-d are not used in comparisons

Example

input file1
-----------
xxxxxxxxxxxxxx000001xxxxxxxxx.....
xxxxxxxxxxxxxx000012xxxxxxxxx.....
xxxxxxxxxxxxxx000014xxxxxxxxx.....
xxxxxxxxxxxxxx000031xxxxxxxxx.....
xxxxxxxxxxxxxx000039xxxxxxxxx.....

input file2
-----------

000012301001110071110070110064
000012302580110076110075110064
000014400001130260130263130173
000014301502110073110070110064
000014400001130177130174130173
000031300001110066110065110064
000032400001345683989812348989
000039400001130177130174130173
000039400001130260130263130173
000039300525110067110065110064

output file
-----------
xxxxxxxxxxxxxx000001xxxxxxxxx.....NNNNNN
xxxxxxxxxxxxxx000012xxxxxxxxx.....YNNNNN
xxxxxxxxxxxxxx000014xxxxxxxxx.....YNNNYN
xxxxxxxxxxxxxx000031xxxxxxxxx.....YNNNNN
xxxxxxxxxxxxxx000039xxxxxxxxx.....YNNNYN


I see (at manual) that can be used IFTHEN, WHEN, OVERLAY and HIT=NEXT but I don't know how to use. Could you help me?


thanks for your help.
sorry if, perhaps, I have not properly explained

Pulcinella
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Maching with IF clauses

Postby skolusu » Wed Feb 22, 2012 11:01 pm

pulcinella,

Your input sample data does not match the output you show. How did you get a 'y' in ind1-a when none of the records in field amount2-b have '110065' or '110070' or '110078' ?

I just formatted the file-2 for a better readability.
000012 301001 110071
000012 302580 110076
000014 400001 130260
000014 301502 110073
000014 400001 130177
000031 300001 110066
000032 400001 345683
000039 400001 130177
000039 400001 130260
000039 300525 110067


What is the max number of duplicate records do you have per key in file 2?
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Maching with IF clauses

Postby pulcinella » Thu Feb 23, 2012 1:03 am

You're right skolusu. Apologize for the error. The example was that you should make a idea of ​​the input files and output. Possibly made a mistake when entering numbers. What if true, are the conditions that must be met. Would be:

000012 301001 110071
000012 302580 110078
000014 400001 130260
000014 301502 110070
000014 400001 130177
000031 300001 110065
000032 400001 345683
000039 400001 130177
000039 400001 130260
000039 300525 110065

Right now I do not know the number of duplicate keys in the second file. The first file (not duplicates records) has more than 9,000,000 records. The second file is more than 16,000,000
I do not think more than 20 occurrences per key. If you think it is really necessary, I look and I say calmly.

Thank you
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Maching with IF clauses

Postby skolusu » Thu Feb 23, 2012 2:26 am

pulcinella wrote:The second file is more than 16,000,000 I do not think more than 20 occurrences per key.


Pulcinella,

Since you can have up to 20 occurrences per key and the flag is either 'y' or 'n' we need to use 2 bytes to get unique records from file2. In order to do that we pad the end of file2 with 12 zeroes (2 bytes for each flag) and then populate a value of '1' whenever there is match on the amounts. We then sum the those bytes so that we have unique records with counts. Using a change command we translate them to flags of 'Y' or 'N'. Use the following DFSORT JCL which will give you the desired results
//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//INA      DD DSN=Your input LRECL=570 byte fileA,DISP=SHR
//INB      DD DSN=Your input LRECL=30 byte fileB,DISP=SHR         
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                               
  OPTION COPY                                                 
  JOINKEYS F1=INA,FIELDS=(15,6,A)                             
  JOINKEYS F2=INB,FIELDS=(01,6,A)                             
  JOIN UNPAIRED,F1                                             
  REFORMAT FIELDS=(F1:1,570,F2:20,12)
                       
  INREC IFOUTLEN=576,                                         
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=571,INOUT=(C' ',C'0'))),
  IFTHEN=(WHEN=INIT,BUILD=(1,570,                             
          571,2,CHANGE=(2,C'00',C' N'),NOMATCH=(C' Y'),       
          573,2,CHANGE=(2,C'00',C' N'),NOMATCH=(C' Y'),       
          575,2,CHANGE=(2,C'00',C' N'),NOMATCH=(C' Y'),       
          577,2,CHANGE=(2,C'00',C' N'),NOMATCH=(C' Y'),       
          579,2,CHANGE=(2,C'00',C' N'),NOMATCH=(C' Y'),       
          581,2,CHANGE=(2,C'00',C' N'),NOMATCH=(C' Y'))),     
  IFTHEN=(WHEN=INIT,OVERLAY=(571:571,12,SQZ=(SHIFT=LEFT)))     
//*                                                           
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,18,X,12C'0')),                   
  IFTHEN=(WHEN=(13,6,SS,EQ,C'110065,110070,110078'),               
         OVERLAY=(21:C'1'),HIT=NEXT),                               
  IFTHEN=(WHEN=(07,6,CH,EQ,C'300158'),                             
         OVERLAY=(23:C'1'),HIT=NEXT),                               
  IFTHEN=(WHEN=(13,6,SS,EQ,C'100034,100035,100037,100038'),         
         OVERLAY=(25:C'1'),HIT=NEXT),                               
  IFTHEN=(WHEN=(13,6,CH,EQ,C'100014'),                             
         OVERLAY=(27:C'1'),HIT=NEXT),                               
  IFTHEN=(WHEN=(13,6,CH,EQ,C'130177'),                             
         OVERLAY=(29:C'1'),HIT=NEXT),                               
  IFTHEN=(OVERLAY=(31:C'1'),                                       
    WHEN=(13,6,SS,EQ,C'110331,110334,110335,110336,110337,110339'))
  SUM FIELDS=(20,2,22,2,24,2,26,2,28,2,30,2),FORMAT=ZD             
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Maching with IF clauses

Postby pulcinella » Thu Feb 23, 2012 4:19 am

I understand you... I probe the example and tell you.
Thanks!!!
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Maching with IF clauses

Postby pulcinella » Fri Feb 24, 2012 2:09 pm

Skolusu

Excuse me again... Changed the requirements.
the first case if must be "If the field amount2-c = '110065' or '110070' or '110078' write 'Y' in the ind1-a"...
I probe to change the first line "IFTHEN=(WHEN=(13,6,SS,EQ,C'110065,110070,110078'),OVERLAY=(21:C'1'),HIT=NEXT)" with
"IFTHEN=(WHEN=(19,6,SS,EQ,C'110065,110070,110078'), OVERLAY=(21:C'1'),HIT=NEXT)"
but I always obtain a "N" at first column... Can you think may be happening? Should I change the REFORMAT command? Try to understand the example you gave me but if I could clarify I'd appreciate it

input file1
xxxxxxxxxxxxxx000001xxxxxxxxx.....
xxxxxxxxxxxxxx000012xxxxxxxxx.....
xxxxxxxxxxxxxx000014xxxxxxxxx.....
xxxxxxxxxxxxxx000031xxxxxxxxx.....
xxxxxxxxxxxxxx000039xxxxxxxxx.....

inputfile2
000012301001110071110070110064
000012302580110076110075110064
000014400001130260130263130173
000014301502110073110070110064
000014400001130177130174130173
000031300001110066110065110064
000039400001130177130174130173
000039400001130260130263130173
000039300525110067110065110064

output file
xxxxxxxxxxxxxx000001xxxxxxxxx.....NNNNNN
xxxxxxxxxxxxxx000012xxxxxxxxx.....YNNNNN
xxxxxxxxxxxxxx000014xxxxxxxxx.....YNNNYN
xxxxxxxxxxxxxx000031xxxxxxxxx.....YNNNNN
xxxxxxxxxxxxxx000039xxxxxxxxx.....YNNNYN

The remaining cases have not changed... Only changed the first case I should ask for the field amount2-c instead of-mount2-b... I thought that this small change would be enough but I must be doing something wrong

Excuse me!!!
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Maching with IF clauses

Postby BillyBoyo » Fri Feb 24, 2012 2:21 pm

Can you show all the sort cards you are using and your inputs/expected output, in the Code tags, please.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Maching with IF clauses

Postby skolusu » Fri Feb 24, 2012 9:48 pm

pulcinella wrote:Skolusu

Excuse me again... Changed the requirements.
the first case if must be "If the field amount2-c = '110065' or '110070' or '110078' write 'Y' in the ind1-a"...
I probe to change the first line "IFTHEN=(WHEN=(13,6,SS,EQ,C'110065,110070,110078'),OVERLAY=(21:C'1'),HIT=NEXT)" with
"IFTHEN=(WHEN=(19,6,SS,EQ,C'110065,110070,110078'), OVERLAY=(21:C'1'),HIT=NEXT)"
but I always obtain a "N" at first column... Can you think may be happening? Should I change the REFORMAT command? Try to understand the example you gave me but if I could clarify I'd appreciate it

The remaining cases have not changed... Only changed the first case I should ask for the field amount2-c instead of-mount2-b... I thought that this small change would be enough but I must be doing something wrong


The reason you are not getting the right results is because , there is NO amount2-C field in the job I gave. I trimmed it down to just key and the 2 amounts since they are the only 2 fields you wanted to check. Just changing the IFTHEN statement will not work as there is no field to be evaluated to begin with. I guess you need all the 4 amount fields , in case in future you decide to add more conditions. You need 2 changes .

1. Change the JNF2CNTL to the following

//JNF2CNTL DD *                                                       
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(31:12C'0')),                       
  IFTHEN=(WHEN=(19,6,SS,EQ,C'110065,110070,110078'),                   
         OVERLAY=(32:C'1'),HIT=NEXT),                                 
  IFTHEN=(WHEN=(07,6,CH,EQ,C'300158'),                                 
         OVERLAY=(34:C'1'),HIT=NEXT),                                 
  IFTHEN=(WHEN=(13,6,SS,EQ,C'100034,100035,100037,100038'),           
         OVERLAY=(36:C'1'),HIT=NEXT),                                 
  IFTHEN=(WHEN=(13,6,CH,EQ,C'100014'),                                 
         OVERLAY=(38:C'1'),HIT=NEXT),                                 
  IFTHEN=(WHEN=(13,6,CH,EQ,C'130177'),                                 
         OVERLAY=(40:C'1'),HIT=NEXT),                                 
  IFTHEN=(OVERLAY=(42:C'1'),                                           
    WHEN=(13,6,SS,EQ,C'110331,110334,110335,110336,110337,110339'))   
  SUM FIELDS=(31,2,33,2,35,2,37,2,39,2,41,2),FORMAT=ZD                 
//*


2. Change the reformat statement from REFORMAT FIELDS=(F1:1,570,F2:20,12) to REFORMAT FIELDS=(F1:1,570,F2:31,12,?)
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Maching with IF clauses

Postby pulcinella » Sat Feb 25, 2012 1:17 am

All right. Thank you for your help. I not only had to change the sentenceREFORMAT but the sentence INREC too.
Ok. I examine the example and manual for understand better.
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post