Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

expression similar to vlookup or something different?

Hi All,

I’m very new and I need a quick help, I hope.

I need to make an expression similar to excel vlookup. I need to multiply working “hours” for  “rates”, where rates depend on “who “ spent the hours working.

At first I believe I succeeded but then I see that totals don’t work. I guess there is some “logical” mistake but I don’t understand where is it.

Can you help me?

I attach an example file.

..second.. what is the best way to learn expression? Is there somewhere a list of example of most used  an useful ones?

Thank you in advance

Sisar

1 Solution

Accepted Solutions
MVP
MVP

Re: expression similar to vlookup or something different?

You're right - the totals failed because the rates changed.  As for why, a pivot table doesn't do a sum of rows, but instead reevaluates your expression for the total rows.  When it finds multiple rates that apply, it simply nulls out the field, which is almost never what you want.  You can use your expression to explicitly tell QlikView to do a sum of rows, but the syntax is annoying and will probably be difficult to remember the first few times you have to use it:

sum(aggr(your expression, your dimensions))

So for your example:

sum(aggr(sum(hours)*rate,who,month))

For example expressions, there's the help inside of QlikView itself, and the forum is swimming with them.  Finding the RIGHT example in either place is, of course, a challenge.

3 Replies
Not applicable

Re: expression similar to vlookup or something different?

I add a picture... what I don't understand is why it works for Mrs Blue and it doesn't for Mr Red. Because of rates changes? but why? and how to solve it?

thank you

sisar

example.JPG

MVP
MVP

Re: expression similar to vlookup or something different?

You're right - the totals failed because the rates changed.  As for why, a pivot table doesn't do a sum of rows, but instead reevaluates your expression for the total rows.  When it finds multiple rates that apply, it simply nulls out the field, which is almost never what you want.  You can use your expression to explicitly tell QlikView to do a sum of rows, but the syntax is annoying and will probably be difficult to remember the first few times you have to use it:

sum(aggr(your expression, your dimensions))

So for your example:

sum(aggr(sum(hours)*rate,who,month))

For example expressions, there's the help inside of QlikView itself, and the forum is swimming with them.  Finding the RIGHT example in either place is, of course, a challenge.

Not applicable

Re: expression similar to vlookup or something different?

Great! It works. thank you so much.

Sisar

Community Browser