Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
if you are unable to join the Factor, you can use this:
=aggr( sum(aggr( count(RoleName), RoleName, ID_Type)*Factor_CarType), RoleName)
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
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.
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
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.
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
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.
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:
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)))
if you are unable to join the Factor, you can use this:
=aggr( sum(aggr( count(RoleName), RoleName, ID_Type)*Factor_CarType), RoleName)