Calculated Field (ApplyMap) Three Tables from different Sources

[Mitch_Data]

Hello,

I try to explain it thoroughly. I want to create a calculated field between three different tables, that is made with multiple IF statements.

What have I done so far? I don't know if it is right so please give your ideas!

I have one field called "Grey License Plate" originated from a different section in my load editor 
So From [Book Value List - due to company mentions, I won't mention the exact name] you know the drill.

I have one field called "Appraisal Value External" originated from section [Sale Additional].

Then lastly, I have "Rest BPM" originated from section [Restal BPM].

-----------------------------------------------------------------------------------------------------------------------------------------------------

I want to create a new calculated field within the Restal BPM section. So up until now I have created two Mapping Tables:

MapTable2:
Mapping Load
appraisalvalueexternal,
licenseplate
FROM [SALE ADDITIONAL]
;

MapTable3:
MAPPING LOAD
licenseplate,
greylicenseplate
FROM [BOOKVALUELIST]
;

Then I created a table with all the fields I need, I don't know if this is required? (they are all connected through licenseplate )

Table2:
LOAD *,
licenseplate,
appraisalvalueexternal,
RestBPM,
greylicenseplate
FROM Table2;

From here I want to create a calculated field by using Applymap and IF statements together:

IF(appraisalvalueexternal = '0', '0') 1st requirement
IF(greylicenseplate <> 'Grey', (appraisalvalueexternal + RestBPM) / 1.21 +(appraisalvalueexternal + RestBPM)

so basically if the license plate is not grey (meaning not  a commercial vehicle) then I want to the calculation based on the above statement.

My only problem is how do I add ApplyMap to this function so I don't get a load error. 

Something like this?

IF(ApplyMap('MapTable2', appraisalvalueexternal='0', '0'  - I don't know how to proceed.

Thanks in advance guys!

[rubenmarin1]

Hi, how "Rest BPM" is related to other tables,? wich fields do you have in this table?

To use the mappings and do the calculation you'll need at least a table with appraisalvalueexternal and RestBPM populated for each row.

Then in the same "Rest BPM" table you can load the other  2 values using applymap,ie:

[Rest BPM]:
LOAD
  *,
  ApplyMap('MapTable3', licenseplate,Null()) as greylicenseplate
;
LOAD
  RestBPM,
  appraisalvalueexternal,
  ApplyMap('MapTable2', appraisalvalueexternal,Null()) as licenseplate,
  ...OtherFields
...From/Resident [Rest BPm Origin]...;

With this you can do the calculation just as:

IF(appraisalvalueexternal = '0', '0', 
  IF(greylicenseplate <> 'Grey', (appraisalvalueexternal + RestBPM) / 1.21 +(appraisalvalueexternal + RestBPM))