Creating New column

Sarath27

Hi All,

I need a help in this situation.

I have three distinctive level values 1,2 & 3. For each run, any of these level values might not come. I have a formula which sums Level 1 + Level 2 + Level 3.

So in my case, Level 3 is not coming for this run, so formula tool throws an error as Level 3 is missing.  I want to write a dynamic formula where if any Level 1, 2, 3 is missing then it needs to create a new column and populate null values in that column. It seems to be really complex.

Pls kindly help.

Sample.PNG

Formula.PNG

flying008

Hi, @Sarath27 

2 ways of dynamic solution for you:

A- If field not exist, it still can normal calculation to get result, but don't add new columns for miss.

B- It create all form to calculate for all field to union at first, then flow your formula to get output.

 

Input
Region	Product	Level-1(External Assets)	Level-2(External Assets)
Americas	Bond - Corporate	18509	16943959
Americas	Margin Lending		4077551233
Closed	Margin Lending		0
EMEA	Bond - Corporate		17983329
EMEA	Government bond - Other - Europe	-197404	12375890
UN	Test	1	2
Output -A
Region	Product	Level-1(External Assets)	Level-2(External Assets)	External Assets
Americas	Bond - Corporate	18509	16943959	16962468
Americas	Margin Lending		4077551233	4077551233
Closed	Margin Lending		0	0
EMEA	Bond - Corporate		17983329	17983329
EMEA	Government bond - Other - Europe	-197404	12375890	12178486
UN	Test	1	2	3
Output -B
Region	Product	Level-1(External Assets)	Level-2(External Assets)	Level-3(External Assets)	External Assets
Americas	Bond - Corporate	18509	16943959	0	16962468
Americas	Margin Lending	0	4077551233	0	4077551233
Closed	Margin Lending	0	0	0	0
EMEA	Bond - Corporate	0	17983329	0	17983329
EMEA	Government bond - Other - Europe	-197404	12375890	0	12178486
UN	Test	1	2	0	3