Using Independent Input file in formula control

[LadyNaureen]

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 solution]

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 solution]

@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