Header and Detail Records

[ncsridhar]

I don't know the answer to ur Qn - but then again, to attack even the basic problem - how do we skip rows that are not data. For example, one of the files I have to deal with has some human readable header content like, for example, no. of transactions in the file or something like that. Now, I know that it takes an N no. of lines everytime and also its going to be first N no. of lines of the file, but then how to get skip those lines at the top and start parsing from a pre-specified line number?

The reason I ask this question, is that you said you thought that there is going to be only one header at the top in your case (which sounded like if it were the case, you are ok!)

Thanks!

[ray.wurlod]

This is probably most easily solved with three separate targets containing some sortable keys to allow the data to be merged subsequently. What form is your source data in?

[I_Server_Whale]

Hello Ray,

I have achieved part of the problem, but not the whole solution yet. The source is a fixed-width flat file. And then there exists the header, detail and tender for each transaction. Each transaction has one header record, may have one or more detail record(s) and one tender record.

The way I'm implementing my job to achieve varying record length target file format is as follows:

1. I have written a routine 'VarRecLen' which takes in all the target fields as its arguments.

2. It concatenates the fields required for the header record and then concatenates with char(13):char(10) {for DOS}.

3. It then concatenates the above string (in 2.) with the fields required for the detail record and then concatenates with char(13):char(10) {for DOS}.

4. Finally, it concatenates the fields required for the tender record in the same way.

5. The most important point though is that my target is a sequential file with no delimiter and no quote character.

6. So, my job has one source seq. file, one transformer (where I call the routine) and the target seq. file.

But, I have one last major hurdle. Since, each transaction may have multiple 'detail' records. Now, I'm able to generate one header, one detail and one tender record of varying record lengths. But I'm stuck on how to generate those multiple detail records if I encounter the same transaction number more than once.

I hope my explanation is comphrensive and decipherable.
Zillion thanks!

Whale.

[amsh76]

Naveen,

Every time when you try to populate the detail record, you need to check that field of header record and then populate detail record...I think you need to have a If condition.

[I_Server_Whale]

Hi amsh76(Amit),

If it was not for you. I would have not reached this far. But How will I check the present input (transaction no.) with the previous input(transaction no.)?

The logic may be something like this (In the constraints):

IF NEW.transaction.no = OLD.transaction.no

THEN strip the header and tender, load only detail record.

ELSE continue regular process.

Hope that will do it. But how do I implement this in DS program?

Thanks much!
Whale.

[pnchowdary]

Hi NaveenD,

Its Simple, use stage variables like shown below

Code: Select all

Derivation                     StageVar
NEWVAL                          OLDVAL
INPUTVAL                        NEWVAL

Then use both the above variables which has old transaction no (OLDVAL) and the new transaction no (NEWVAL) to check your condition

[I_Server_Whale]

Hi pnchowdary,

I'm using stage variables, but still not getting the right answer. I'm using 6 stage variables.

1. HEADER ---> calls the header routine.

Code: Select all

trial.header(DSLink3.f1, DSLink3.f2, DSLink3.f3, DSLink3.f4, DSLink3.f5, DSLink3.f6)

2. DETAIL ---> calls the detail routine.

Code: Select all

trial.detail(DSLink3.f7, DSLink3.f8, DSLink3.f9, DSLink3.f10)

3. TENDER ----> calls the tender routine.

Code: Select all

trial.tender(DSLink3.f11, DSLink3.f12, DSLink3.f13)

4. OLDTRAN ----> mapped to the TRANSACTION NUMBER field in the source file.

Code: Select all

DSLink3.f1

5. NEWTRAN ----> equal to OLDTRAN.

6. RESULT

Code: Select all

If (OLDTRAN <> NEWTRAN) Then (HEADER : DETAIL : TENDER) Else DETAIL

so, when the old transaction number is not equal to the new one, I should load a new header, new detail and a new tender record.

Now I'm passing the 6th Stage Variable to my ONE field in the target file.

But when I run the job, although I have duplicate TRANSACTION NUMBERS in the source file. It is just loading the multiple SKU's in the target. Not the header and tender. That means it is executing only the ELSE part of the logic above.

Any help is very much appreciated.

Thanks!
Whale.

[pnchowdary]

Hi NaveenD,

I have used a similar technique in a lot of jobs. It should work for you too. Try to use the Debugger and monitor the values of OLTRAN and NEWTRAN. That will give you an idea of why its not working.

[I_Server_Whale]

Got it. Thanks a lot.

Whale.

[pnchowdary]

Hi NaveenD,

Good that it works for you now. :D Why wasn't it working earlier?. What was the mistake?