To check the sequence of the input file
Moderators: chulett, rschirm, roy
To check the sequence of the input file
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.
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.
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
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
The Brute
-
- Charter Member
- Posts: 88
- Joined: Tue Jan 13, 2004 3:07 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
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
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
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
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
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
-
- Participant
- Posts: 28
- Joined: Mon Jan 09, 2006 1:31 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.
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.
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
The assumptions here are,
1) Data is sorted on the key field.
2) Trailer record of a data set always follows its detail records.
With regards,
Thiru
Thiru
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
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