Using Independent Input file in formula control

Question

Dear Experts

I need your expert advice on resolving following:

Below is a scenario:

Input 1 --> have fields --- Investment_Instrument , Invesment_Amount
Input 2 global_parameters --> have fields --- run_date, min_limit, max_limit, investment_rate

There are two tables and we have to produce output: 
Investment_Instrument , Invement_Amount , profit_earned

We have 1 formula field: Profit_earned

Profit_earned = investment_amount * invesment_rate [where investment_amount between min_limit and max_limit]

Problem statement: 
Apparently we cannot use JOIN control or UNION control  between 2 inputs - as I tried both methods and it did not work.
How can I use fields from Input 2 in formula control of Input 1?

KarolinaRoza · Accepted Answer

Hi @LadyNaureen,

If I understand your problem correctly you want to have new column 'Profit_earned' which use data from two data source but you do not have a common key to join this data.

You can:

- create 'dummy' column (field) with for example value =1

- use Join tool to join both data sources,  but here you will have all combinations of data form 1st and 2nd source

- then you can apply your formula

let me know if it helps

Karolina

LadyNaureen · Accepted Answer

@KarolinaRoza

Thank you for prompt response.

This approach is good but the problem is there is not only 1 independent table but multiple independent tables. This will create messy cross joins and hard to work on.

LadyN