Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Excel vlookup - like function in charts for qlikview/sense

Good morning,

I was wondering if we can tackle my challenge with the power of the qlik-community.

We have fact data and some dimension in one table to simplify everything  (please see attachment).

Suppliers deliver material and some of parts are defective, we build KPI quantity defective divided by quantity received multiplied by 10000.

I have to compare the calculated KPI with the reference table which provides the status e.g. A, B, C or D depending on the value of the KPI. e.g. if the KPI value is 30 the status is A.

The reference table is a loosen table in the data model.

I can´t calculate the status in the data model, cause I don´t know what filter in the UI the business user will apply.

So I am looking for kind of "vlookup"-Function (as known from Excel) to use as chart function in qlikview, to look up values in the reference table and get back the status value after the user have made selections they need.

As a result I would like to have e.g. the "status" for selection user have made by months

The solution with "if" it is not preferable, cause the reference table in the real example (not simplified) has more than 100 different status.... so it will be a lot of "if"s to write, to manage and to calculate...

hic  first of all, thank you very much for the great sessions last week in Orlando. We talked about this challenge, so probably you have some ideas how to solve it.

Thank you in advance, Regards, Mikhail

1 Solution

Accepted Solutions
sunny_talwar

Are you planning to use a cycle group or drill down group? You can do something like this

=Aggr(If((sum(quantity_defective)/sum(quantity_received))*10000 > value_from and

  (sum(quantity_defective)/sum(quantity_received))*10000 < value_to, status), $(='[' &GetCurrentField(Cycle_Group) & ']'), status)

1) Yes and remove month (unless you want to aggregate it at month and region)

2) As long as the chart dimension and Aggr() dimension match (status is island table dimension so it doesn't really count), It will always give one and only one value (if status doesn't have overlapping to and from values) so you don't have to worry too much about this. Just make sure to include all your dimension in the Aggr() function's dimension....

View solution in original post

7 Replies
sunny_talwar

You might be able to use Aggr() function, but can you provide some selections based on which you expect to see 30 and A?

sunny_talwar

May be like this

=Aggr(If((sum(quantity_defective)/sum(quantity_received))*10000 > value_from and

  (sum(quantity_defective)/sum(quantity_received))*10000 < value_to, status), month, status)

Capture.PNG

Anonymous
Not applicable
Author

Hi Sunny,

thank you very much. I´ll try to verify the solution and have to think what kind of virtual table is created with aggr-function once we put month & status from not associated table as dimension....  is it a Cartesian product?

The result of the formula, you provided, should probably be the table with the same values, because we don´t have an outer aggregation, so it´s implicitly applied Only() function. As far as I can understand, if I want to see not only by month but e.g. by country as a dimension in a chart, I need to add it to the Aggr function probably.... to keep the values the same for the outer aggregation Only() function.

=Aggr(If((sum(quantity_defective)/sum(quantity_received))*10000 > value_from and

  (sum(quantity_defective)/sum(quantity_received))*10000 < value_to, status), month, status)

sunny_talwar

To answer you first question, this definitely is a Cartesian product.

To answer your second question, it all depends on what level are you looking to do your aggregation? Is it Month, Region or a unique identifier field? When I say aggregation, I mean this

(Sum(quantity_defective)/Sum(quantity_received))*10000

Need this to be carried out at Month or Region level before it gets compared to the value_from and value_to in order to find the status?

Anonymous
Not applicable
Author

.... Need this to be carried out at Month or Region level before it gets compared to the value_from and value_to in order to find the status?

Yes, could be,  it is not fixed yet... I just try to find a flexible or easy adjustable solution.

Correct me if I am wrong, 1) but if I need to carry out at Region additionally, I just have to put Region in the Aggr-function as dimension additionally? 2) we need to make sure that the result of the AGGR (....) function is just

the same value in all lines, cause it is basically ONLY(AGGR()) and if the value are not the same, we´ll get an error?

sunny_talwar

Are you planning to use a cycle group or drill down group? You can do something like this

=Aggr(If((sum(quantity_defective)/sum(quantity_received))*10000 > value_from and

  (sum(quantity_defective)/sum(quantity_received))*10000 < value_to, status), $(='[' &GetCurrentField(Cycle_Group) & ']'), status)

1) Yes and remove month (unless you want to aggregate it at month and region)

2) As long as the chart dimension and Aggr() dimension match (status is island table dimension so it doesn't really count), It will always give one and only one value (if status doesn't have overlapping to and from values) so you don't have to worry too much about this. Just make sure to include all your dimension in the Aggr() function's dimension....

Anonymous
Not applicable
Author

Hi Sunny, thank you, let me please verify with my real example, because I simplified the question in order not to make the explanation 5 pages long. I just need to play with it a bit and brush-up the knowledge about aggr-function....