Mapping Load from multiple fields

[Anonymous]

My Order data has a list of ordered procedures in a semicolon-delimited field, ex:

OrderId, ContractID, Procedures

1000001, Ctrct1, 'Proc1;Proc3;Proc4'

1000002, Ctrct2, 'Proc2;Proc4'

My Pricing data has, by ContractID, a price field for each possible procedure, ex:

ContractID, Proc1, Proc2, Proc3, Proc4

Ctrct1, $100, $250, $500, $75

Ctrct2, $95, $270, $400, $100

I am trying to price each order by summing the values from the relevant columns based on which procedures are present in the Order.

My intuition is to use a SUBFIELD to break out the procedures:

OrderProcedure:

LOAD OrderID, ContractID, SubField( [Procedures], ';') as Procedure

Resident Orders;

If I could then get the Pricing info into a mapping table, I could do the following:

OrderProcedure:

LOAD *, ApplyMap('MappingTable', [ContractID] & '-' & [Procedure], 0) as Price

From OrderProcedure ;

Left Join (Orders)

LOAD OrderID, Sum(Price) as [Order Price]

Resident OrderProcedure group by OrderID ;

The challenge is loading the mapping table, which should be populated as

MappingTable:

Mapping LOAD * INLINE [

CtrctProc, ProcPrice

Ctrct1-Proc1, $100

Ctrct1-Proc2, $250

...

Ctrct2-Proc3, $400

Ctrct2-Proc4, $100

However, I'd want to do this systematically rather than inline, in effect "transposing" the procedure names from horizontal (field names) to vertical (values in the lookup field). Would a Crosstable accomplish this? Or is there a simpler way to populate the Order Price field?

Thanks for any help you can provide.

[marcus_sommer]

I think your thoughts are right and it would be exactly the same what I would do:

- loading contracts with crosstable-statement

- mapping load on this transformed table by combining of contract-id and proc

- using applymap within a subfield-load on the order-table

- Marcus