topic Re: Multiplying multiple variables against a field in QlikView

Multiplying multiple variables against a field

Wed, 12 Oct 2016 21:54:09 GMT

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

Re: Multiplying multiple variables against a field

sunny_talwar — Wed, 12 Oct 2016 23:52:50 GMT

May be try bringing the if statement within the Sum and see what happens:

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))))))))))
))

Re: Multiplying multiple variables against a field

marcus_sommer — Thu, 13 Oct 2016 06:33:08 GMT

I think Sunny is right - here a small adjustment to his suggestion:

sum(Multi*(KM/100)
*(pick(match(PhaseTypeYearProvinceNAICS, 'ConstructionGas2016Alberta486', 'ConstructionGas2016British_Columbia486', 'ConstructionGas2016Saskatchewan486','ConstructionGas2016Quebec486','ConstructionGas2016Northwest_Territories486','ConstructionGas2016Ontario486', 'ConstructionGas2016Manitoba486','ConstructionGas2016New_Brunswick486','ConstructionGas2016Nova_Scotia486','ConstructionGas2016Yukon486', '*'),
$(v1), $(v2), $(v3), $(v4), $(v5), $(v6), $(v7), $(v8), $(v9), $(v10), $(v11))

- Marcus

Re: Multiplying multiple variables against a field

Thu, 13 Oct 2016 13:38:05 GMT

Yes, Sunny's suggestion is working now. Thank you very much!

Marcus, I have never seen that formula before. After looking at it, it makes sense to me though.

Does this run faster than having all the nested if statements?

One reason I like the nested if statements is that I can set a final else-if statement for all other rows in that column to go to zero without having to write in all 600 formulas for each row in like it seems I would have to do with the pick match method.

Re: Multiplying multiple variables against a field

sunny_talwar — Thu, 13 Oct 2016 16:43:32 GMT

Pick is usually better to understand, smaller in length and you can def. set a final false condition like this

Pick(Match(FieldName, 'Value1', 'Value2') + 1, 'FalseValue', 'TrueValue1', 'TrueValue2')

The above is equivalent to this

If(FieldName = 'Value1', 'TrueValue1',

If(Fieldname = 'Value2', 'TrueValue2', 'FalseValue'))

Re: Multiplying multiple variables against a field

marcus_sommer — Thu, 13 Oct 2016 17:43:23 GMT

Pick(match()) will definitely be faster than a nested if-loop which calculates at first all branches (no break after the first true-result like in other tools) and evaluates afterwards which branch contained the true-value. I'm not sure how match() will be processed but you save at least the condition-part which will be executed only once.

- Marcus

Re: Multiplying multiple variables against a field

Fri, 14 Oct 2016 14:54:16 GMT

I ended up going with the pick match method because I also hit the nested if loop maximum. It was roughly around 100 - figured it after removing several at a time until it worked - and I needed about 300.

Pick match is working perfectly!!

Thanks again, very much appreciated!

Re: Multiplying multiple variables against a field

Mon, 17 Oct 2016 17:20:59 GMT

I am having a problem again. I have updated all my expressions, and when double checking the functionality the charts no longer update when changing the variable now that I am using the Pick Match method it seems.

As an example:

My expression includes this variable:

$(ceDemandGasConstruction)

Which in turn is defined in my script as:

SET ceDemandGasConstruction = sum(Multi*(KM/100)*(Pick(Match(PhaseTypeYearProvinceNAICS,

'ConstructionGas2015Alberta486',

'ConstructionGas2016Alberta486',

'ConstructionGas2017Alberta486',

'ConstructionGas2018Alberta486',

'ConstructionGas2019Alberta486',

'ConstructionGas2020Alberta486',

'ConstructionGas2021Alberta486',

'ConstructionGas2022Alberta486',

'ConstructionGas2023Alberta486',

'ConstructionGas2024Alberta486',

'ConstructionGas2025Alberta486',

'*'),

$(KMConstructionGas2015Alberta486),

$(KMConstructionGas2016Alberta486),

$(KMConstructionGas2017Alberta486),

$(KMConstructionGas2018Alberta486),

$(KMConstructionGas2019Alberta486),

$(KMConstructionGas2020Alberta486),

$(KMConstructionGas2021Alberta486),

$(KMConstructionGas2022Alberta486),

$(KMConstructionGas2023Alberta486),

$(KMConstructionGas2024Alberta486),

$(KMConstructionGas2025Alberta486))));

I then have input boxes with each of these variables.

But as stated, now the chart is not updating as the variables are updated.

What am I missing??

Re: Multiplying multiple variables against a field

sunny_talwar — Mon, 17 Oct 2016 17:42:30 GMT

Would you be able to share a sample (with dummy data) to see the issue?

Thanks,

Sunny

Re: Multiplying multiple variables against a field

Mon, 17 Oct 2016 19:46:59 GMT

Attached is a demonstration.

I recreated most accurately how I am doing it in my actual document. For some reason it does not update when I use a variable to include the expression.

Any ideas?

Re: Multiplying multiple variables against a field

sunny_talwar — Mon, 17 Oct 2016 20:03:16 GMT

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)))

Re: Multiplying multiple variables against a field

Mon, 17 Oct 2016 20:16:45 GMT

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?

Re: Multiplying multiple variables against a field

sunny_talwar — Mon, 17 Oct 2016 20:23:43 GMT

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

Re: Multiplying multiple variables against a field

Mon, 17 Oct 2016 20:53:14 GMT

This is working!

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

Re: Multiplying multiple variables against a field

sunny_talwar — Mon, 17 Oct 2016 21:14:17 GMT

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

Best,

Sunny