topic Set Analysis and Variables? in QlikView

Set Analysis and Variables?

Tue, 19 Jul 2011 13:58:02 GMT

All-

How do I set up set analysis (with variables) to sum up specific values contained in the output of the variables? Here's an example expression:

=sum({<Product.ProdName={'$(vProd1)'}>}
Product.ProdValue) + sum({<Product.ProdName={'$(vProd2)'}>}
Product.ProdValue) + sum({<Product.ProdName={'$(vProd3)'}>}
Product.ProdValue) + sum({<Product.ProdName={'$(vProd4)'}>}
Product.ProdValue)

The above expression works absolutely fine. I need a totals column. That being said, the values contained in vProd1, vProd2, (so on...) contain either a "ProdA" or "ProdB" contained in the ProductName (which is pulled in based on the variable). I need to sum up all values containing "ProdA" and sum up all values containing "ProdB" each in it's own column. What's the best way of accomplishing this?

Also... I have to imagine this can be highly intensive (or could be). I've provided a highly shorted version of this expression above. Additionally, there are always hundreds of Products being added everyday. So, I don't want to run into an "Out of memory" situation. So also looking for efficient ways of handling this situation as well.

Thanks!

Set Analysis and Variables?

swuehl — Tue, 19 Jul 2011 14:29:30 GMT

Hi,

you can use set expression also with search strings, like

sum({<Product.ProdName={"*ProdA*"}>} Product.ProdValue)

which should limit the ProdNames to those containing ProdA.

Alternatively, you might want to have a look at the wildmatch() function, you can call functions from set expression using dollar sign expansion.

Stefan

Set Analysis and Variables?

swuehl — Tue, 19 Jul 2011 14:34:49 GMT

me again.

I am just curious:

Do you use this kind of expressions to compare a user selection (of 4 products) to total numbers?

If you tell us a bit more about your business requirement, I think someone here in the forum might be able to answer your performance question.

Stefan

Set Analysis and Variables?

Tue, 19 Jul 2011 14:44:25 GMT

Hey swuehl-

Thanks so much for your help, that being said:

=sum({<Product.ProdName={'$(vProd1)'}>} Product.ProdValue)

vProd1 is actually a variable. I have an input selector where the user defines the value of the field. So, using your example that you presented... how would one adjust the expression?

Thanks!

Set Analysis and Variables?

swuehl — Tue, 19 Jul 2011 15:18:37 GMT

Well,

it should look like

=sum( {<Product.ProdName = {"*$(vProd1)*"} >} Product.ProdValue)

if I understand your question correctly (I was aware of vProd1 being a variabel, I assumed that you use a literal for the total match like 'ProdA'.

My Variable vProd1 is defined as

='ProdA'

for example

Stefan

Set Analysis and Variables?

Tue, 19 Jul 2011 15:26:12 GMT

Sorry, I'm not giving enough info.

the vProd1 variable actually is a drop down selector. The Product Names (which appears in the drop down selector) are already defined in a table. Within the Product Name itself contains either "ProdA" or "ProdB."

So essentially, it goes like this:

1. User has a drop down input selector. He/she chooses a Product: ShoesProdA.

2. ShoesProdA appears in the straight table with a value of 4. Essential vProd1 (the variable) gets replaced with ShoesProdA, with a value of 4.

The user has up to 30 products that they can select (in my example, I only went up to 4 hence: vProd1, vProd2....)

Hope that clears things up.

Set Analysis and Variables?

swuehl — Tue, 19 Jul 2011 15:40:03 GMT

With all the variables around, I think I missed the bottom line

We started with a need for an expression for total column, summing up all ProdValues for ProdNames containing a certain search string, right?

I think that you could do that with either using a literal or variable.

Do we still have a problem at this place?

I don't understand "Essential vProd1 (the variable) gets replaced with ShoesProdA, with a value of 4."

Does that mean you rename the variable to its value (Variablename vProd1 gets ShoesProdA and new value is numerical value of 4)? If so, what's that for?

If you still got problems and in case it is feasible for you, please post a small sample file, I am really curious now...

Regards,

Stefan

Set Analysis and Variables?

Tue, 19 Jul 2011 18:06:57 GMT

"We started with a need for an expression for total column"

I've already done this with the expression I originally posted.

"summing up all ProdValues for ProdNames containing a certain search string, right?"

Yes... this is *exactly* what I'm looking for.

Within the Product Names, it contains either "ProdA" *or* "ProdB"

"I think that you could do that with either using a literal or variable.

Do we still have a problem at this place?"

To define the Products, I'm using a variable called vProd1, vProd2, vProd3, vProd4.

These variables reference an input selector, when you click on the drop down (let's say for vProd1), a large dropdown list of Products appear. The user will select one of those products in the dropdown. Within the *name* of the Product in the dropdown that the user selects will contain either "ProdA" or "ProdB."

"I don't understand "Essential vProd1 (the variable) gets replaced with ShoesProdA, with a value of 4."

Does that mean you rename the variable to its value (Variablename vProd1 gets ShoesProdA and new value is numerical value of 4)? If so, what's that for?"

No, we don't rename anything, the user selects a Product from a dropdown list which is what defines the variable vProd1, vProd2, vProd3, or vProd4.

Re: Set Analysis and Variables?

Tue, 19 Jul 2011 18:32:18 GMT

Here's my sample QVW (and it's data) to help where I'm comin' from

I'd like to create a "ProdA" Total column and a "ProdB" Total column.

Re: Set Analysis and Variables?

swuehl — Tue, 19 Jul 2011 20:07:29 GMT

Ok, now I think I get want you want...

...Something like attached?

Not the most elegant way, but your task is really a bit tricky.

Have a nice evening,

Stefan

Re: Set Analysis and Variables?

swuehl — Tue, 19 Jul 2011 20:24:31 GMT

me again,

I just checked, for total of the four selections,

you could also write expression like

=sum({<Product={'$(vProd1)'}+{'$(vProd2)'}+{'$(vProd3)'}+{'$(vProd4)'}>} ProductValue)

instead of the four sums (like you used in OP).

And, to filter only ProdA containing variables, you might use

=sum({<Product=({'$(vProd1)'}+{'$(vProd2)'}+{'$(vProd3)'}+{'$(vProd4)'})*{'*ProdA*'}>} ProductValue)

(ProdB is trivial, of course)

Maybe this helps,

Stefan

Re: Set Analysis and Variables?

Tue, 19 Jul 2011 20:40:08 GMT

Stefan-

Thank you so much for all your help today. *Really* appreciate it. This definitely did it!

Thanks so much.

Re: Set Analysis and Variables?

swuehl — Tue, 19 Jul 2011 21:46:55 GMT

You're welcome. I am glad we got a good solution for an interesting problem.

Good night,

Stefan