To check the sequence of the input file

srividhya · Post by **srividhya** » Mon Feb 13, 2006 6:29 am

Hi,

In a CSV file, I have to check the sequence of the file.
The file is having lot of seperate set of data. each set should have detail records followed by the trailer record. For example, the below is set of data.But the same set can repeat in the file. The detail records always have there last three fields null, where the trailer record always have the first 3 fields and last three fields.

1,abc765,iutri,,,,ro , ret ,rt , re, rt, , ,
1,abc765,tiur,,,,r t,rtr,er r,rr,rrw, , ,
1,abc765,djdk, , , , , , , , , , , ,1234,1234,1567.

Thanks in advance,
Sri.

tardifma · Post by **tardifma** » Mon Feb 13, 2006 7:28 am

Hi.
Sorry but, can u explain us exactly what sequence you want to verify... I'm not sure of what exactly you're trying to do???

Thanks

srividhya · Post by **srividhya** » Mon Feb 13, 2006 8:03 am

Thanks for your response.

I have to check the sequence. If any trailer is not there, or the trailer is between the details, then i have to reject the file. and abort the job.

Thanks
Sri

tardifma · Post by **tardifma** » Mon Feb 13, 2006 8:30 am

Hi...
I think I know what you're saying now.

You have a multiple set of records that contains detail records and one trailer record that resume the detail records of your set.

And you want to validate that the trailer record summarized correctly the detail records...

Then, to do so, I would use a transformer with stage variables.
In the stage variable section, you could define variables current_key, old_key, current_record, total_value... and follow this kind of logic:

If old_key=current_key then
if current_record is trailer then
if total_value = trailer.value then
ok
else
Abort
else
total_value = total_value + current_record.value
else
total_value=0

Hope this will help

Inquisitive · Post by **Inquisitive** » Mon Feb 13, 2006 10:26 pm

For below approach to work, we need to run Transformer in sequential mode right?

tardifma wrote:Hi...
I think I know what you're saying now.

You have a multiple set of records that contains detail records and one trailer record that resume the detail records of your set.

And you want to validate that the trailer record summarized correctly the detail records...

Then, to do so, I would use a transformer with stage variables.
In the stage variable section, you could define variables current_key, old_key, current_record, total_value... and follow this kind of logic:

If old_key=current_key then
if current_record is trailer then
if total_value = trailer.value then
ok
else
Abort
else
total_value = total_value + current_record.value
else
total_value=0

Hope this will help

rasi · Post by **rasi** » Mon Feb 13, 2006 11:11 pm

srividya

You need to run this job in sequential mode and with the use of stage variables apply your business rule to find out the record type(Trailer, Detail).

Regards

kumar_s · Post by **kumar_s** » Tue Feb 14, 2006 6:53 am

Hmm.. Iam not user whether srividhya requires the total amount to be reconciled. But i guess she just want to check the sequence of the flow such as detail records and trailer records in the order.

If iam right....

Hi srividya,

How do you define a set? i.e., How many detail records has one trailer records? How can you identify the missing trailer?

-Kumar

srividhya · Post by **srividhya** » Wed Feb 15, 2006 9:51 am

Hi Kumar,

You are correct i dont need to check the total, Just i want to check the order.

The count of detail records are not fixed , it may be 2records in a set or 6 records in a set. But there should be only one trailer for a set.

Thanks
Sri

reachthiru · Post by **reachthiru** » Wed Feb 15, 2006 3:14 pm

Hi Srividhya,

Still you can follow whatever tardifma's logic with a little change. create fields current_key,old_key, current_last_field and old_last_field. Even you can use your input fields instead of defining current_key and current_last_field.

Now define each field like this in the same sequence.

Code: Select all

if old_key='' then old_key=current_key
else
   if old_last_field is null then <raise error to abort the job>
   else
       old_key=current_key

Code: Select all

old_last_field=current_last_field

So for first record it will assign the current record to old record as well. From the 2nd record onwards, it will start doing the comparison and if the keys are not matching and the last read record is not a trailer record, then the job will abort. Hope this will solve ur problem.

The assumptions here are,
1) Data is sorted on the key field.
2) Trailer record of a data set always follows its detail records.

kumar_s · Post by **kumar_s** » Wed Feb 15, 2006 9:18 pm

Hi Srividya,

Still you yet to let us know, how to identify the missing trailer?

If trailer was missing after two detail and was foudn after 4 detail records(which is actually the trailer for the last two detail records), how is that going to be pointed out. Is there any filed in trailer which as the count of the total number of detail records in the set?

-Kumar