intime trans type
22.23.12.9 pay
22.23.20.5 pay
23.24.12.6 pay
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 pay
23.25.22.9 pay
23.26.22.9 ATM
23.26.22.9 CHQ
23.26.22.9 CHQ
23.26.22.9 CHQ
23.26.22.9 CHQ
23.28.22.9 CHQ
The file is comma delimited variable block file .for understanding the requirement i have shown in formatted way as above
Format of in-time: HH:MM:SS:ms
There are millions of records in file .
Requirement:
What I need to do is
Calculate number of transaction logged in one minute time frame for each payment type
for e.g. start time in file is 22.23 and before it switched to 22.24 i.e for first 1 min
it has below result
start of time no of trans of Pay-type no of trans of ATM type no of trans of CHQ type
1 2 0 0
now for next one min it has below transaction
start of time no of trans of Pay-type no of trans of ATM type no of trans of CHQ type
2 1 0 0
again for next one minute it has belwo transactions
start of time no of trans of Pay-type no of trans of ATM type no of trans of CHQ type
3 02 10 0
now see the last transaction its time is 23.28 and befor it there is no entry for 23.27
that means there is no transaction at 23.27 so it should be added as zero as belwo
start of time no of trans of Paytype no of trans of ATM type no of trans of CHQ type
3 0 0 0
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
pdighade,
Answer the following questions.
1. What is the LRECL and RECFM of the input file?
2. What is the position of the TIME field?
3. What is the position of the type field?
4. Why is the start of time a 1 byte seqnum? How are you going to relate it to the actual times?
5. You want a default value for missing records. How do you map which time records are missing.
Ideally I would have generated the report as follows
1. What is the LRECL and RECFM of the input file?
Ans:file is with LRECL =1028 & RECFM=VB
2. What is the position of the TIME field?
Ans: Time field is the first field of file so it will start form position 5 th
as it is VB file
3. What is the position of the type field?
Ans : Payment type field is start on 170th position and is of length 3
4. Why is the start of time a 1 byte seqnum? How are you going to relate it to the actual times?
Ans:Actually from the report I need to generate graph for time against no of transaction of pay types. Ideally this file is having 24 hrs of data. say from 00.01 to 23.59 . I just take first time in file whatever it may be say 00.01 so my next minute will be 00.02 and so on.. and i want to capture no of transactions in between this time slot .
5. You want a default value for missing records. How do you map which time records are missing.
Yes for missing records i want default value as zero for all payment types
In report shown by you there is no entry for 23.27 . Simple way to map is from starting time period in ur case 22.23 ,keep adding 1 min to it and find for transaction with that period if not found then default zero to that period
I do have tendency of posting bad or inefficient solution but here is the solution which I could come up with. When tested, it gave me correct output...
Since, I couldn't come up logic to add records where there is no payment posted, I am creating a master file with just HH:MM value for a day and then matching that with your payment file from first step. For demo, my input master file contains only few records.
However, I tested the job with instream input data, so I may be off by 1 or 2 bytes in some places. Feel free to correct me.
one thing i paytype filed's position is getting changed for few records
So instead of below
INREC IFTHEN=(WHEN=(06,03,CH,EQ,C'ATM'),OVERLAY=(51:41,08)),
IFTHEN=(WHEN=(06,03,CH,EQ,C'CHQ'),OVERLAY=(61:41,08)),
IFTHEN=(WHEN=(06,03,CH,EQ,C'PAY'),OVERLAY=(71:41,08))
can I use SS (scan) option as below along with "when"?
As my Transaction type filed is not of fixed length i have inserted above logic . It is giving correct result for T1 file
but for T2 file which splits the transaction of different type with their count is not working
IN PREVIOUS TOPIC I FORGET TO MENTION NAME OF ONE THE TRANSACTION TYPE IS PAYMENT INSTAED OF PAY .i.e all transaction types are not of same length
one is of 7 char and rest all are of 3 charcters
I have a question from my attempt of this question for RESTART on multiple keys
1) Is there a way in DFSort, we can perform RESTART for multiple keys?
For example, I have FIELD1 through FIELD5 and I need to add SEQNUM RESTART logic based on FIELD1 and FIELD3. Currently, I am moving these fields to the end of the record and then performing RESTART. Is there a better/efficient way?
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
sqlcode1 wrote:
Kolusu,
I have a question from my attempt of this question for RESTART on multiple keys
1) Is there a way in DFSort, we can perform RESTART for multiple keys?
For example, I have FIELD1 through FIELD5 and I need to add SEQNUM RESTART logic based on FIELD1 and FIELD3. Currently, I am moving these fields to the end of the record and then performing RESTART. Is there a better/efficient way?
Thanks,
The Restart parm needs the fields to be consecutive. So you need to move the fields F1 and F3 together and then use the RESTART parm. Just so you know you the Restart logic will NOT work in this case as there can be multiple breaks in data. For ex if the times are like this.
Code:
23:01
23:03
23:04
23:06
23:09
23:11
23:22
When coding a solution think of ALL the possible ways that you can break that solution and then you will come up with an ideal solution.
I ran this job with all possible time values and it worked. Since OP wanted report at the minute level. In my input I had records for no transaction,1 transaction,2 transactions and all 3 transactions. Here my intention was to break the Sequencing whenever payment type changes.
From your example if we have all ATM transaction for below time examples, it would have 1 in SEQNUM for all of them.
now, if we have multiple transaction for different payment type in a minute, it would still assign seqnum of 1 to all the records,which when used with TRAILER3 would give us COUNT of total occurences.
hh:mm
23:01 ATM
23:01 CHQ
23:04 ATM
23:04 CHQ
I apologize if my understanding is still wrong or I am asking asking too much but I really didn't get the part where this RESTART logic wouldn't work.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
sqlcode1 wrote:
I apologize if my understanding is still wrong or I am asking asking too much but I really didn't get the part where this RESTART logic wouldn't work.
sqlcode1,
*sigh* If your intention of using the RESTART parm is to count the occurrences of each type within minute timeframe , then you really don't need it in the first place because trailer3 can automatically get the count of occurrence for each type using COUNT parm
Secondly you are assuming that you can ONLY have 1 ATM or 1 PAY or 1 CHQ transaction within a minute time frame which is wrong. what happens if the data is like this
Code:
hh mm ss type
23:01:03 ATM
23:01:06 PAY
23:01:09 ATM
23:01:12 CHQ
23:01:15 ATM
23:01:25 CHQ
23:01:35 PAY
23:01:45 PAY
Now your restart parm will create
Code:
hh mm ss type seqnum
23:01:03 ATM 00000001
23:01:15 ATM 00000002
23:01:09 ATM 00000003
23:01:12 CHQ 00000001
23:01:25 CHQ 00000002
23:01:35 PAY 00000001
23:01:45 PAY 00000002
23:01:06 PAY 00000003
Now if you use that as a count your counts are clearly off and you would get wrong results.
Please take time to understand the problem and then you can solve the problem.
and second thing is ( There is slight change in requirement . The change is file may contain data of previous day's transaction as well. and we need to consider these transactions also in our calculation. The file may contain few transactions of previous day as it depends upon time at with transaction report is generated . We can identify transactions of particular date by date fields in file which is 6 char long(yyyymmdd) and start at position 13th so logic in first step may not work here. I am really apologizes for inconvenience caused due to changed requirement . This will be my final requirement. Can you please help me with this?
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
pdighade wrote:
Hi Skolusu,
I tried with your code
Input file is having below sample data
22:55 CHQ
22:56 ATM
It is showing the result but entry for time is getting repeated as below
pdighade,
If you have copied the control cards I provided as IS , you will NEVER get the duplicated rows. You need to show me what you changed. Please don't claim that the solution provided gave you wrong results. If you claim you are getting the wrong results, I want to see the control cards you used and your COMPLETE sysout of the job.
Quote:
and second thing is ( There is slight change in requirement . The change is file may contain data of previous day's transaction as well. and we need to consider these transactions also in our calculation. The file may contain few transactions of previous day as it depends upon time at with transaction report is generated . We can identify transactions of particular date by date fields in file which is 6 char long(yyyymmdd) and start at position 13th so logic in first step may not work here. I am really apologizes for inconvenience caused due to changed requirement . This will be my final requirement. Can you please help me with this?
What am I supposed to do with the previous day transactions? Generate another 1440 records for previous day also? what is the expected output? I can't read your mind nor have access to your data. Your date field is 6 character long and how did you fit a 8 byte format in it?
Job is working fine and now giving expected result
Actually I did mistake in below line
C'.',81,8,ZD,MOD,+60,M11,LENGTH=2,1020:X))
As my input file is having date in format with ':' in between HR:MM
I have replaced c'.' with c':' and its working.
Answers for your questions.
What am I supposed to do with the previous day transactions?
Ans:For all the transactions of previous day, all the resultant rows of time and count should come just before start of whatever date next to it.
Generate another 1440 records for previous day also?
Ans: No of transactions of previous day to be captured for perticular time slot depends upon our requirement .Whenever requied we set some parameter and capture transaction of previous day for perticular time slot along with
current days trans. One more thing the time slot of previous day is always in continuation with current day.
e.g previous day's 22:00 PM to 24:00 hrs of current date
So whaever trans of previous day. the count should just come before start of date next to it
Note: The current date is not always system date. we can extract transaction of any date and the date just previous to it. But the limit is
max two days data we can extract and We are generally never extracting previous day's complete data.
Your date field is 6 character
long and how did you fit a 8 byte format in it?
Ans:This is mistake from my side. The data is having 8 characters.
and start position is 13th and format is yyyymmdd
So generating 1440 records for previous day may not work here as file may not have complete day's trans. No of records to be generated depends upon time slot captured of previous day.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
pdighade wrote:
So whaever trans of previous day. the count should just come before start of date next to it
pdighade,
The following DFSORT JCL will give you the desired results. The report now shows as date field next to the time. The report will generate the time report from the beginning time frame of any date and generate any default values for the missing time slots till 23:59 of each day.