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

[edwin]

QLikView automatically does the vlookup for you assuming you built your data model correctly (linking your two tables using  ID TYpe from what i see).
if i understand your data model correctly, you have a table with individual role names where each row is a single count.  so each row (role =A for example) will be associated to type 1 and will have the associated factor 12.5.
you dont need to count the rolename anymore just sum the factor.

=sum(Factro_CarType)

if you still need the SA {$<Status={2}>} , add it in.  however, since your tables dont show it, i cant tell if this will cause an issue seeing status in the set analysis is 2, but the table says finished.  i suggest try it first with out the set analysis

or show us the table where you have status

[george55]

Thanks, but only Sum the Factor with Status in it will bring even a smaler number then the column "Count".

This is the correct formular for the column "Count", which is a measure:

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

I m using as dimension "RoleName" and "Status"

Just need to enhance to get the "Count weighted". It meas, the Datamodel is correct and I need all information in the formular above.

[edwin]

the fact that you actually do a count indicates, your data model does not have a count column.  hence one row is a single count so you just do a sum.

however, if Quantity is a table with a  column called COUNT as in your example, then i would use the following expression:

=aggr(sum({<status={2}>} aggr(NODISTINCT sum({<status={2}>} Count)*Factor_CarType, RoleName, ID_Type) ), RoleName)

if you can attach a picture of your data model tables, that will clarify things

[george55]

correct, the Count-Column in my Table above is a calculated column just zu show for demostration. Can not provide the Datamodell, because my case above is a simple example. The real Datamodell ist much more complicated.

You new Aggr-Formula does also not help. Maybe I should pre-calculate the count weigted in the script, rather then using set analysis. Will try tomorrow. 

[edwin · Accepted solution]

in that case, and maybe this is what you meant:

CarType:
load * inline [
ID_Type,Name,Factor_CarType,
1,BMR,12.5,
2,Tyote,10.0,
];

Quantity:
load * inline [
ID_Type,RoleName,Count
1,A,10,
1,B,500,
2,A,50,
2,B,600
];

left join (Quantity)
load
ID_Type,
Factor_CarType as Factor
resident
CarType
;

in your chart: 

=sum(Count * Factor)  // of course here you can add what set analysis you need as your data model would surely be more complex than the script above

[george55]

Sorry, may be I was unclear. "Count" in the table below is the result in the User-GUI, calculated with the formula below. It is a Table shown in the User-GUI. The real Table in the Data repository in Qlik is the green edge. Hope now it is clear.

For better understanding: "Count" is the count of Records in the Table.

I believe the result must be a Aggr function.

[edwin]

The approach to this will depend on the data model.  sharing a picture will short cut all the guessing. 

here is my next guess (note i dont have status - ill let you add that in.  but the solution is the same)

CarType:
load * inline [
ID_Type,Name,Factor_CarType,
1,BMR,12.5,
2,Tyote,10.0,
];

Roles:  //this is just create enough sample rows
load *, rowno() as Rnum inline [
ID_Type,RoleName
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
2,B,
1,A,
1,B,
2,A,
2,B,
1,A,
1,A,
1,A,
1,A,
1,A,
1,A,
1,A,
1,A,
1,A,
1,A,
1,A,
1,A,
];


left join (Roles)
load
ID_Type,
Factor_CarType as Factor
resident
CarType
;

since you have Factor with the RoleName you can just sum Factor.  the following shows you what i mean:

[Steven_Haught]

@george55 

I have done a similar look up to a set value table such as yours using IF and Only. Maybe could use here in a manner such as this? Not good to use if you have many more RoleNames though. 

IF(
YourMeasurefortheCount=
Only({1<RoleName={'A'}>}Count),
Only({1<RoleName={'A'}>}CountWeighted)

,

IF(
YourMeasurefortheCount=
Only({1<RoleName={'B'}>}Count),
Only({1<RoleName={'B'}>}CountWeighted)))

[edwin · Accepted solution]

if you are unable to join the Factor, you can use this:

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