Load Script help to manipulate data in one to many relationship

[pduplessis]

I have header and detail level data in a one to many relationship with a good key linking the 2 tables, but there is repeated data at the detail level, that really should be at header level.

I would like to bring the field from the detail level into the header table when I load the data.

For example

HeaderTab:

load

Invoice#,

.......

.......

DetailTab:

load

Invoice#,

InvoiceDate,

............

........

is there an easy way to get InvoiceDate into the HeaderTab without an outer join putting everything together, then loading distinct Invoice#, InvoiceDate later?

Thanks for your wisdom

[disqr_rm]

DetailTab:

load

Invoice#,

InvoiceDate,

............

........

mapInvoiceDate:

MAPPING

LOAD DISTINCT

Invoice# as MatchString,

InvoiceDate as MatchValue

RESIDENT DetailTab;

HeaderTab:

load

Invoice#,

applymap('mapInvoiceDate', [Invoice#], null()) as InvoiceDate,

.......

.......

But you may want to delete InvoiceDate from DetailTab to avoid synthetic keys.

Hope this works for you.

[pduplessis]

Rakesh, thanks for the reminder on doing a mapping load, but since then, I understand better why the data is at the detail level (those pesky exceptions), and since they want to select on more of them, I have decided not to mess with the data model, and have used instead set analysis to limit the selection of data in the header table based on data in the detail table. We will soon see how expensive that is! best regards

[disqr_rm]

I kinda knew it, but I still wanted to give you some idea how can combine the data. There are many other ways you can do this, but best is to leave Header-Detail tables and corresponding fields in their respective tables, unless you have a specific requirement.

Take care.