expression similar to vlookup or something different?

[#1]

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

[#2]

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

[johnw · Accepted solution]

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.

[#4]

Great! It works. thank you so much.

Sisar