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.
