Date gap check using sort or ICETOOL

N.NIRMALRAJ · New User Joined: 09 Jun 2015 Posts: 4 Location: INDIA

Hi All,

I am stuck up the following requirement need expert advice,

Input file:

First 3 bytes account number next 10 bytes date

1212014-12-31----
1212014-11-30----
1212014-10-31----
1212014-09-30----
1212014-08-31----
1212014-07-31----
1212014-06-30----
1212014-05-31----
1212014-04-30----
1212014-03-31----
1212014-02-28----
1212014-01-31----
1212013-12-31----
1212013-11-30----
.
.
.
2013 complete data
.
.
.
2012 incomplete data (not 12 months)
.
.
.
2011 complete data
1222014-12-31----
1222014-11-30----

Expected output:

Complete data until first gap.

(i.e)

For 121 account 2014,2013 -> should omit from 2012 onwards even though 2011 has complete data.

I tried the following sort card in ICETOOL,

SELECT FROM(IN) TO(OUT) ON(1,7,CH) EQUALS(12) USING(CTL1)

CTL1 -> SORT FIELDS = COPY

but the output has all accounts having 12 months data not sure how to check the gap using sort.

Bill Woodger · Posted: Thu Jul 16, 2015 5:33 pm

Just do a simple SORT with a COPY operation and have an INCLUDE for the years that you want. As far as I can tell. Otherwise, more explanation of what the problem is and sample of expected output.

Terry Heinze · Posted: Thu Jul 16, 2015 6:48 pm

N.NIRMALRAJ,
In the future, please learn to use Code tags to make your data more readable.

enrico-sorichetti · Posted: Thu Jul 16, 2015 6:56 pm

the description might be foggy due to the language barrier...
but the problem seems clear enough to me

unless also the seniors members want to be spoon fed to the last bit with the requirement description

the TS has a dataset with some groups of end of month data

the TS wants to copy all the complete 12 months groups until an incomplete group is found

magesh23586 · Posted: Thu Jul 16, 2015 9:00 pm

Couple of assumption
1. LRECL = 80
2. Maximum number of records 99999999

Here is the code of for you. The trick here is to use same input file in INA and INB

N.NIRMALRAJ · New User Joined: 09 Jun 2015 Posts: 4 Location: INDIA

Requirement:

Need to copy all the accounts having complete 12 months data until first gap found.
Trying to achieve this in sort or icetool.

Eg:

acc date
---**********
1212014-12-31 /\
1212014-11-30
1212014-10-31 ||
1212014-09-30
1212014-08-31 ||
1212014-07-31
1212014-06-30 c
1212014-05-31 o
1212014-04-30 p
1212014-03-31 y
1212014-02-28
1212014-01-31 t
1212013-12-31 h
1212013-11-30 i
1212013-10-31 s
1212013-09-30
1212013-08-31 r
1212013-07-31 a
1212013-06-30 n
1212013-05-31 g
1212013-04-30 e
1212013-03-31
1212013-02-28 ||
1212013-01-31 \/
1212012-12-31
1212012-11-30
1212012-10-31
1212012-09-30
1212012-08-31\
1212012-06-30/ date gap
1212012-05-31
1212012-04-30
1212012-03-31
1212012-02-28
1212012-01-31
1212011-12-31
1212011-11-30
1212011-10-31
1212011-09-30
1212011-08-31
1212011-07-31
1212011-06-30
1212011-05-31
1212011-04-30
1212011-03-31
1212011-02-28
1212012-01-31

2014,2013 -> should omit from 2012 onwards even though 2011 has complete data.

Thanks Mahesh will try your approach.

Bill Woodger · Posted: Fri Jul 17, 2015 1:04 am

Is there only one account?

RahulG31 · Active User Joined: 20 Dec 2014 Posts: 446 Location: USA

Are the months always in order, even for imcomplete set?

I mean that the incomplete set can consist of months 12,11,10,9,8,7,6 (rest from 5 are not present) OR it can be like 12,10,7,3,1. That is months are missing in between.

.

N.NIRMALRAJ · New User Joined: 09 Jun 2015 Posts: 4 Location: INDIA

Files can have multiple accounts and the data is sorted as Account (ascending) and mont end dates (descending).

Mahesh,

Tried your sord card it only copied one account..trying to change your logic.

Bill Woodger · Posted: Fri Jul 17, 2015 2:00 pm

N.NIRMALRAJ,

Yes, that's why we asked. Your data showed something different.

You also should address RaghulG31's question about what is missing when something is missing. Can the entire year be missing? Can there be "gaps" within data which is present. Where there is data but it is busted, is it still in descending date sequence.

You'll need to change the key on Maghesh's contribution (1,3,A,4,4,D instead of 1,7,D) and then you'll need to "reset" the "don't use any more records" flag for the first record of a new account (not the first record of a new account/year).

N.NIRMALRAJ · New User Joined: 09 Jun 2015 Posts: 4 Location: INDIA

Thanks Mahesh & Bill..Just changed few things in sort card accordingly and it worked like gem..Hats off to you people..