VLookup in Qlik

[george55]

Hi all,

Would like to do a simple VLookup in Qlik. Situation:

Please see 3 Tables below. Goal: Get Column "Count Weighted". Tried with this formular but get empty values:

Count({$<Status={2}>} RoleName) * Factor_CarType

Thanks for any help

CarType
ID_Type	Name	Factor_CarType
1	BMR	12.5
2	Tyote	10.0
Quantity
ID_Type	RoleName	Count
1	A	10
1	B	500
2	A	50
2	B	600
Pivotable
Status	RoleName	Count	Count Weighted
finished	A	60	625
	B	1100	12250

[george55]

@edwin: Your last expression worked. Thanks.

Next step: Would like to show a Chart History. The Aggr() Function is good for one aggregated figure, not for a time series. How should Implemed that? Can also open a new thread?

[edwin]

if you want the aggregation by Date and Rolename, just add the date to the aggr:

=aggr(NODISTINCT sum(aggr( count(RoleName), RoleName, ID_Type, Dt)*Factor_CarType), RoleName, Dt)

[george55]

Does not work. Chart is empty

[george55]

Thanks Edwin.

With you App, did you see the problem below? Have some gaps and believe dont need a muliplicator with the factor anymore. A Sum of the Factor should do it. 

Believe it is not neccsary anymore to 

[george55 · Accepted solution]

This is the solution:

Sum({$<RoleName={2}>} Factor)

[edwin]

there are two solutions embedded in the QVW i sent you - one copies the Factor to the main fact table so you dont need to look it up.  this was what i suggested the first time so you just need to sum the factor as your transaction records have a unit count = 1 (you dont need the count you just sum the Factor).
2nd solution is if you cant add Factor into your fact table, use the expression with the AGGR function.  your choice and it appears youve made it.