7 Replies Latest reply: May 24, 2017 7:23 AM by Mikhail Barannikov RSS

    Excel vlookup - like function in charts for qlikview/sense

    Mikhail Barannikov

      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

        • Re: Excel vlookup - like function in charts for qlikview/sense
          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?

            • Re: Excel vlookup - like function in charts for qlikview/sense
              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

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

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

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

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