Multiplying multiple variables against a field

[#1]

Hello,

I have two fields that I am multiplying against each other.  Each field is in two different tables but are synced through a unique identifier field.   When I multiply them I have the following basic formula.

sum(KM*(Multi/100))

My problem is that for each row of the KM field I want to multiply a different variable against it.   I used dynamic update to do this originally but cannot use it on a server.  I want to give the ability to basically change the KM field.

My first attempt was the following with each variable.

sum(Multi*(KM/100))
*(
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Alberta486',300,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016British_Columbia486',122,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Saskatchewan486',80,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Quebec486',3,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Northwest_Territories486',0,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Ontario486',3,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Manitoba486',40,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016New_Brunswick486',0,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Nova_Scotia486',0,
if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Yukon486',0,
10))))))))))
)

Note: 300 in the first if statement would actually be a variable and each other number below would be a variable as well that the user could change.

The problem here is that unless I selected one of the strings in that unique identifier field it would just multiply everything by 10.  Which I don't want.  It would work, but I had to select as an example 'ConstructionGas2016Alberta486' and it would only work for that one.

I then though why not just create a specific set analysis statement for each row instead of the if statement which I tried the following.

300*sum({<PhaseTypeYearProvinceNAICS={'ConstructionGas2016Alberta486'}>}KM*(Multi/100))

This worked a bit better but would still give me data for provinces that were not associated with Alberta as an example. 

There are 600 rows in the KM field with a variable already created to adjust it.  Originally the variables were changed as a number you wanted to add or subtract from the "baseline" value in the KM field and then you a created a dynamic update script that would add/subtract the table based on updated variables.

Any ideas on how to replace the dyanmic update method would be extremely helpful!!

Thanks

[sunny_talwar]

If you use this as your variable definitation, it seems to be working fine. Isn't it?

Sum(KM*Multi*Pick(Match(Multiply, 'Yes', '*'), $(vYes)))

[#3]

Yes it is working, but I just realized that I have the formula defined in the load statement so when I reload it - which is needed to be able to reset all the variables people will be changing - it sets the variable (the formula) as the number at the time of reload.

I just changed it to:

Set vExpression = Sum(KM*Multi*Pick(Match(Multiply, 'Yes', '*'), vYes));

Now it looks like it is working even after having it reloaded.   Is there any downside to removing the $() from the variable that will be used for the expression?

[sunny_talwar]

In this particular case, there should not be any issue since you are using plain numbers. If you were using expression, then it might have caused you issue. But you can continue to use dollar sign expansion like this:

Load * Inline [

Multi, KM, Multiply

2, 4, Yes

3, 6, No

3, 7, Yes

2, 3, No

];

Set vYes = 4;

Set vNo = 2;

Let vExpression = 'Sum(KM*Multi*Pick(Match(Multiply, ''Yes'', ''*''), $' & '(vYes)))';

Note: Two single quotes around Yes and * (they are not double quotes) used as Escape sequences

[#5]

This is working!

Thank you so much for the help again!!  Really appreciate it! 

[sunny_talwar]

I am glad that me and Marcus were able to help you out.

Best,

Sunny