Generating a Key between a Header and Detail Record

[THEDSKID]

I have a flat file that contains a Header and many Detail records. My problem is that there is no primary or foreign key to join the records. All that I know is that all of the detail records that follow a particular header record, belong to that header.

What I would like to do is to create a sequential value that will generate a value that will link each detail record to its' corresponding header. Please see below:

Any help would be appreciated..


****this is what I have****

HXXX XXX XXX XXXXX

DX X XXX X X XXXX

DX X XXX X X XXXX

HXXX XXX XXX XXXXX

DX X XXX X X XXXX

HXXX XXX XXX XXXXX

DX X XXX X X XXXX

DX X XXX X X XXXX

DX X XXX X X XXXX





****this is what I would like to see****

01 HXXX XXX XXX XXXXX

01 DX X XXX X X XXXX

01 DX X XXX X X XXXX

02 HXXX XXX XXX XXXXX

02 DX X XXX X X XXXX

03 HXXX XXX XXX XXXXX

03 DX X XXX X X XXXX

03 DX X XXX X X XXXX

03 DX X XXX X X XXXX

[chulett]

When you say 'this is what I have' does that mean you are currently generating that output? If so, it should be fairly simple to add a couple of stage variables to check for a Header record and increment a counter each time one is encountered.

[kumar_s]

If you already have the detail record following the header record, what else you exactly looking for?

[chulett]

So... you didn't notice any difference between the two examples?

[srinagesh]

Use a stage variable as a counter.. Initialize the value as 1

increment this value based on the First character of the source string ( If Instr(txt,H) = 1 then... StgVar = StgVar + 1)