Loop in database structure

[#1]

Hi Everyone,

I have a problem with a loop in my database structure. I have 2 data feeds and a mapping table:

Feed 1

Account

Currency

SourceSystem

Amount

Feed 2

Account

Currency

SourceSystemID

Balance

MappingTable:

SourceSystem

SourceSystemID

Rather confusingly, there are many SourceSystemID's to one SourceSystem.

This structure creates a synthetic key consisting of Account and Currency, and also creates links via the mapping table for SourceSystem to SourceSystemID - and hence a loop.

I guess to get rid of the loop I need to do some sort of lookup on the mapping table when I load Feed2 so that I can add SourceSystem to that set of data and thereby create a synthetic key consisting of Account, Currency and SourceSystem.

Any ideas on how I do this?

Thanks,

Tony

[johnw]

I think this?

MappingTable:
MAPPING LOAD
SourceSystemID
,SourceSystem
...
;

Feed2:
LOAD
...
,SourceSystemID
,applymap('MappingTable',SourceSystemID) as SourceSystem
...
;