Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mikhailb
New Contributor II

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

Re: Excel vlookup - like function in charts for qlikview/sense

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....

7 Replies

Re: Excel vlookup - like function in charts for qlikview/sense

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

Re: Excel vlookup - like function in charts for qlikview/sense

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

mikhailb
New Contributor II

Re: Excel vlookup - like function in charts for qlikview/sense

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)

Re: Excel vlookup - like function in charts for qlikview/sense

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?

mikhailb
New Contributor II

Re: Excel vlookup - like function in charts for qlikview/sense

.... 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?

Re: Excel vlookup - like function in charts for qlikview/sense

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....

mikhailb
New Contributor II

Re: Excel vlookup - like function in charts for qlikview/sense

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....