Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Multiplying multiple variables against a field

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

1 Solution

Accepted Solutions

Re: Multiplying multiple variables against a field

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

14 Replies

Re: Multiplying multiple variables against a field

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

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

Not applicable

Re: Multiplying multiple variables against a field

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

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

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

Not applicable

Re: Multiplying multiple variables against a field

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!

Not applicable

Re: Multiplying multiple variables against a field

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

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

Thanks,

Sunny

Not applicable

Re: Multiplying multiple variables against a field

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?

Community Browser