Multiple field join 1

[sagobogger]

I have a .foc with 3 multiplier code fields. I need to link the multiplier codes to a multiplier code table which will return the actual multiplier. I'm stuck with this because I can't see how to get 3 values from the multipler table without cloning the table so that each multipler is linked to a different named table.

Simple example:

The data table

Fields
Code number $amount Multipler1 Multipler2 Multiplier3

Data

1323456   $50.00    10         9          14


The multipler table

Fields

Multiplier_code   Mutiplier

Data

09                 1.5      
10                 2.0    
11                 4.6
12                 0.1
13                 0.9
14                 0.3

The ultimate result I need is

new$amount = $50 * 2.0 * 1.5 * 0.3 = $45

 

[craigiep]

Try this. You can join multiple times to the same file and use tags to identify the instance you want.

JOIN MCODE1 IN FILE1 TO MCODE IN MCODE TAG T1 AS J1
JOIN MCODE2 IN FILE1 TO MCODE IN MCODE TAG T2 AS J2
JOIN MCODE3 IN FILE1 TO MCODE IN MCODE TAG T3 AS J3

DEFINE FILE FILE1
NEW_AMT/D15.2 = AMOUNT * T1.MULTIPLIER * T2.MULTIPLIER * T3.MULTIPLIER;
END

 

[sagobogger]

Cool, thanks!