topic Re: Advanced Expressions as Variables in QlikView

Advanced Expressions as Variables

djbloiss — Wed, 25 Nov 2020 16:16:04 GMT

I have been transferring all the expressions that the company uses over to variables that are saved in Excel. Simple ones like this work no problem: (In Excel)

sum({<$1, $2, $3, $4, $5, $6, $7, $8, $9>} [Item sales-net])

and then call it inside Qlikview like this:

$(e_Sls($(vSO_Closed_Only)))

This becomes a problem in 2 areas.

First, there are certain places that we are passing more than 9 arguments in Set Analysis and if I increase the number of arguments with $10, $11, etc. it is interpreted as $1(0), $1(1), etc. Passing the second digit to the $1 argument. is there a way I can get more than 9 variables passed?

Second, if I am passing a variable that is outside the set analysis, it won't work. I tried the following: (In Excel)

sum({<$1, $2, $3, $4, $5, $6, $7, $8>} $9 [Item sales-net])

and then call it inside Qlikview like this:

$(e_Sls_Test(SO_Closed={1}, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, [Source for PG Report]) TOTAL <[Invoice Number]>)

and

$(e_Sls_Test(SO_Closed={1}, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, [Source for PG Report],,,) TOTAL <[Invoice Number]>)

and

$(e_Sls_Test(SO_Closed={1}, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, [Source for PG Report],Null,Null,Null) TOTAL <[Invoice Number]>)

and

$(e_Sls_Test(SO_Closed={1}, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, [Source for PG Report],,,,TOTAL <[Invoice Number]>))

None of them are working. It is not recognizing the TOTAL <[Invoice Number]> in any of them.

Re: Advanced Expressions as Variables

Anil_Babu_Samineni — Thu, 11 Jan 2018 02:27:55 GMT

Can you check these

Variables with Parameters

Qlikview Parameterised Variables

Re: Advanced Expressions as Variables

marcus_sommer — Thu, 11 Jan 2018 08:54:56 GMT

AFAIK there is no limitation to the number of parameters and I checked it with 12 parameters and it worked like expected - even for each other part of the expression like the TOTAL.

The reason why it didn't work by you is that there are any failures within your expression-variable-construct - one is here: [Customer Terms Code]-={60,90} because there is a comma included and so QV interpret it as two parameters of: [Customer Terms Code]-={60 and 90} which of course leads to an error. There is no way to mask the comma in any way - each comma will be always treated as parameter-delimiter.

In general there are workarounds possible by using a different delimiter for it the inside-comma and replacing it within the variable and/or by using chr(44) within a string-concat - but it won't be always sensible because of the needed complexity.

Saying this I'm not sure that your approach to replace all expressions with variables is a wise decision. Of course it's possible but it adds a huge amount of complexity and additionally efforts to develop and maintain the applications. In my opinion it's an overuse of the idea why variables does exists and for what they are useful.

Nevertheless the above mentioned comma-problem could be bypassed with the following writing:

[Customer Terms Code]-={(60|90)}

- Marcus

Re: Advanced Expressions as Variables

djbloiss — Thu, 11 Jan 2018 13:27:23 GMT

Marcus, what syntax did you use for the 12 parameters? because as I mentioned $10 was interpreted as $1(0) where it viewed that as argument 1 passing 0 to it. And how did you get the Total portion to work? It is not showing up. I am not currently having an issue with the comma. If after I get the formula working, and that is a problem then that would be a good workaround but I am not up to that point yet.

When I look at the label for the expression, I can see how it is interpreted and it is not being interpreted correctly.

Re: Advanced Expressions as Variables

marcus_sommer — Thu, 11 Jan 2018 13:52:53 GMT

I'm just added 11 fields like Year =, Month = to the set analysis and the 12 parameter was TOTAL <Field>. Where did you see that $10 is handled as $1(0) ? Did you look at the expression-interpretation within an empty expression-header of a table-chart?

Further like above mentioned the following [Customer Terms Code]-={60,90} is treated as two parameters, because of the comma in it.

- Marcus

Re: Advanced Expressions as Variables

djbloiss — Thu, 11 Jan 2018 14:22:41 GMT

that isn't what I am asking. You are doing a variable directly in Qlikview.

I am creating this as a variable:

sum({<$1, $2, $3, $4, $5, $6, $7, $8, $9>} [Item sales-net])

or this:

sum({<$1, $2, $3, $4, $5, $6, $7, $8, $9, $10>} [Item sales-net]

Then I am calling the variable like this.

$(e_Sls(Year=, Month=, Day=, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, SO_Closed={1}, SlsPerson=, Special_Offer={2}, Week={52})

that is evaluated like this:

sum({<Year=, Month=, Day=, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, SO_Closed={1}, SlsPerson=, Special_Offer={2}, Year={52}>})

the last argument $10 believes that I am calling $1 with 0 as an argument for it. it ignores the argument that I pass in the 10th place.

"Did you look at the expression-interpretation within an empty expression-header of a table-chart" Yes, that is how I know it is evaluating like that.

Re: Advanced Expressions as Variables

marcus_sommer — Thu, 11 Jan 2018 17:55:09 GMT

Please provide a small example which displayed this behaviour.

- Marcus

Re: Advanced Expressions as Variables

djbloiss — Thu, 11 Jan 2018 18:11:27 GMT

I will put something together. However, I have figured out more in the process. The Total variable is not an issue. Instead the issue you pointed to of the comma being an issue came in to play; what it did was split the comma separated values in to two separate arguments making the total amount of arguments more than I declared in the expression variable. So now I am working on that and the issue of passing more than 9 arguments to the variable.

Re: Advanced Expressions as Variables

djbloiss — Thu, 11 Jan 2018 18:33:38 GMT

Here is my Expression as a variable:

sum({<$1, $2, $3, $4, $5, $6, $7, $8, $9, $10>} [Item sales-net]

Here is what my expression in Qlikview:

if(ItemForPanGregorian AND [Chain store code]='PAN', $(e_Sls_Test(sum, SO_Closed={1}, [Source for PG Report]=, [Customer Terms Code]-={60|90}, [Chain store code]={'PAN'}, Item=, '', '', TOTAL <[Invoice Number]>)))

and the picture shows what qlikview evaluates it as.

as you can see the $10 is being evaluated as sum(0), not, as in this case blank.

Re: Advanced Expressions as Variables

djbloiss — Thu, 11 Jan 2018 18:46:00 GMT

and here is a very basic qvw that shows $10 always evaluates as 1(0)

Re: Advanced Expressions as Variables

djbloiss — Thu, 11 Jan 2018 18:48:45 GMT

how come there is no way to attach a qvw? I can't use advanced editor? I am trying to add the example.

Re: Advanced Expressions as Variables

djbloiss — Thu, 11 Jan 2018 19:01:56 GMT

I figured out why I couldn't add a qvw file. I was replying from my inbox, which doesn't allow you to get in to the advanced editor. Sorry, as you can see in the file $10 always evaluates as $1(0)

Re: Advanced Expressions as Variables

marcus_sommer — Thu, 11 Jan 2018 20:01:27 GMT

For me it worked with 10 parameters although I changed a bit the places of the parameters because there wasn't enough fields in your example so that must be a bit creative. Further I believe it will always work if there are no errors within the variable and the expression-call of it:

- Marcus

Re: Advanced Expressions as Variables

djbloiss — Thu, 11 Jan 2018 20:17:42 GMT

I want to try to replicate that but you don't show how you call the variable exponent.

Re: Advanced Expressions as Variables

djbloiss — Thu, 11 Jan 2018 20:26:51 GMT

Marcus, Thank you. I want to try to replicate that but you don't show how you call the variable exponent.

Re: Advanced Expressions as Variables

marcus_sommer — Thu, 11 Jan 2018 20:27:25 GMT

It's:

$(e_Sls(1, [Invoice Number]=, DateKey=, [Customer Code]=, [Order Date]=, [Transaction Date]=, [Week Count]=, [Ext Sales - Net]=, TOTAL, <DateKey> ))

If you opened the question in a new browser-tab you will see that there is also a qvw attached.

- Marcus

Re: Advanced Expressions as Variables

djbloiss — Thu, 11 Jan 2018 20:53:11 GMT

sorry I didn't realize you supplied a qvw file. with that file and more testing I have discovered more. Here I remove the last 2 arguments and the $10 is evaluated as 1(0), so it only happens if the $10 argument is not passed. That is the issue. This variable will be used in certain cases where there is no 10th argument and in those cases it will result in 1(0) which messes up the expression. I am doing more testing, thank you for your help. I will reply as I figure out more.