I apologize in advance for the formatting. I have the following data in the fact tables:
Account SigneData TimeID CostCenter Customer Platform
ASSESSMENTPCT 0.12 20090000 NO_CC ALASKA_EXPL OVERHEAD_PLATFORM
ASSESSMENTPCT 0.34 20090000 NO_CC CANADA_EXPL OVERHEAD_PLATFORM
ASSESSMENTPCT 0.25 20090000 NO_CC L48_EXPL OVERHEAD_PLATFORM
ASSESSMENTPCT 0.06 20090000 NO_CC NORWAY_EXPL OVERHEAD_PLATFORM
ASSESSMENTPCT 0.08 20090000 NO_CC UK_EXPL OVERHEAD_PLATFORM
ASSESSMENTPCT 0.15 20090000 NO_CC VENEZUELA_EXPL OVERHEAD_PLATFORM
P_TRAVEL 100 20090100 NH471 ASSESSED_CUSTOMER SST_4D_SEISSVYDGN
P_TRAVEL 200 20090100 NH471 ASSESSED_CUSTOMER SST_SMARTFIELDS
and need to created logic to produce the following results:
Account SigneData TimeID CostCenter Customer Platform
P_TRAVEL 12 20090100 NH471 ALASKA_EXPL SST_4D_SEISSVYDGN
P_TRAVEL 34 20090100 NH471 CANADA_EXPL SST_4D_SEISSVYDGN
P_TRAVEL 25 20090100 NH471 L48_EXPL SST_4D_SEISSVYDGN
P_TRAVEL 6 20090100 NH471 NORWAY_EXPL SST_4D_SEISSVYDGN
P_TRAVEL 8 20090100 NH471 UK_EXPL SST_4D_SEISSVYDGN
P_TRAVEL 15 20090100 NH471 VENEZUELA_EXPL SST_4D_SEISSVYDGN
P_TRAVEL 24 20090100 NH471 ALASKA_EXPL SST_SMARTFIELDS
P_TRAVEL 68 20090100 NH471 CANADA_EXPL SST_SMARTFIELDS
P_TRAVEL 50 20090100 NH471 L48_EXPL SST_SMARTFIELDS
P_TRAVEL 12 20090100 NH471 NORWAY_EXPL SST_SMARTFIELDS
P_TRAVEL 16 20090100 NH471 UK_EXPL SST_SMARTFIELDS
P_TRAVEL 30 20090100 NH471 VENEZUELA_EXPL SST_SMARTFIELDS
Any ideas? I know I can use the LOOKUP or GET functions to get the data, but I am stuck with regard to the fact that I have multiple. Thanks Craig
Update: I may not have been clear, so I wanted to add some notes. This is an allocation, of sorts. The first six records represent the percentage that each customer will be allocated. The last two records represent each of the two amounts, by platform dimension, that must be allocated. The result takes the percentage for each customer, multiplies it by the corresponding amount for P_Travel, and creates a P_Travel amount for each customer for each platform.
Any insight, thoughts, direction, theory, examples, etc. would be greatly appreciated.
Edited by: Craig Tennant on May 27, 2008 11:06 PM