How to populate Foreign key column if it is not identity

[srvu]

I have 1 flat file with 5 diff structures
1st row is header which is 1 for flat file
2nd is pharma record which is one for pharma
from 3 claim records under each pharmacy

i have imported them each time with diff structure

now i have to send them to 3 diff tables
header record to header table
claim to claim table
pharma to pharma table

each table is having PK which is identity
pharma is having PK aswell as FK (header PK)
claim is having PK as well as FK (pharma PK)


How can i populate this FK's in table

shall i use Seq T/R to populate these FK's

 

[srvu]

1st table pk is FK in second table.
How can i populate that column
should i use seq generater for 1sttable.pk and 2nd table.FK

 

[rasETL]

I don't think Informatica is real good processing a file with 3 different input sources. Regardless, I think it would be best to re-structure the file before processing.

It sounds to me as if there is a 1:1 between the header and pharma, then there are 3 claim records. So, if the order is...Header, Pharma, Claim, Claim, Claim, Header, Pharma, Claim, Claim, Claim, etc, you could do the following...

1. Write header(s) to a temporary table with an additional column added - an Identity Column
2. Write pharmas) to a temporary table with an additional column added - an Identity Column
3. Write claim(s) to a temporary table with an additional column added - an Identity Column

4. Then create a concatenated temporary table of the header and pharma where the Identity column is equal.

Then create a concatenated temporary table of the header/pharma combination from (4.) and the claim table where the identity column of the claim rec is...
- three times the Identity Id of the combo row
- three times + 1
- three times + 2

You should end up with One table which has all three records as a row. The header and pharma colums are duplicated for each of the three claim records. That Table can then be used to Update all three tables using Informatica.

Hope that makes sense, and it helps.

 

[rasETL]

Note - for below, the last table should match up the Identity column as follows...

- three times the Identity Id of the combo row
- three times - 1
- three times - 2

So, Identity Value 1 will use id 3, 2, 1
Identity Value 15 will use id 45, 44, 43

Sorry for the confusion

 

[srvu]

Thank you for the reply.

Any way i have completed the Task.
Actually my file is in the format
Header,pharm,claim,claim,claim,pharm,claim,claim,pharm,claim,claim.

1 header for every file, which will have batch no.
Pharm records 1 for each pharmacy which is also having batch no and pharm no.
claim records under each pharmacy.
claim records will have pahrm no which is used for identifying from which pharma it is.

so, i have 1 st loaded data into header table which contains PK

Then used header table as LKP, and used batchno in join condition
Header record and pharm record are having batch no

Then populated pharm table and used it as LKP table for claims mapping
In claims mapping join is on pharm no, pharm record and claim record is having pharmno.

As it is Fixed width file and there are different formats for each record i had lot of confussion at the beginning.

Any way managed to complete work successfully.
Thanks for u r reply Rasanders.