6 Replies Latest reply: Jan 3, 2018 4:51 AM by Sunny Talwar RSS

    Show missing value items only

    Ivo Petrov

      Merry Christmas to all of you,
      I'm pretty new in here and with the Qlik Sense as well, so I'd like to say sorry in advance, if my questions looks stupid.
      Here is my issue.

       

      I have 2 tables, connected by ProductID field.

      Table 1: PRODUCT_LIST

      ProductID

      ProductName

       

      Table 2: SALES

      ProductID

      Sales

      Client

       

      I'd like to see which products haven't been bought by the selected Client, but I have no idea how that should be done :/

      Could somebody help me, please!

        • Re: Show missing value items only
          pradosh thakur

          1: filter with dimension Client

          2: KPI/Textbox with =concat({<Client=e(Client)>} ProductName,',')  or  =concat( distinct {<Client=e(Client)>} ProductName,',')

           

          let me know if your requirement is something else.

           

          p.s. Have tried it in qlikview

            • Re: Show missing value items only
              Ivo Petrov

              Hello pradosh and Happy New Year! Let the 2018 be more fruitful and prosperiuos one!

               

              Thank you for your suggestion. It works flawlessly, but I missed the fact, that dealing with hundreds of products it's pretty hard to see what you're looking for. So, in order to be more useful, the data should be organized in groups.

              I've tried to do it, but I failed. So, please have a look at it.

              Here are the tables with the new fields added (in bold)


              Table 1: PRODUCT_LIST

              ProductID

              ProductName

              ProductGroup

              TypeOfProduct

               

              Table 2: SALES

              ProductID

              Sales

              Client

               

              After selecting a Client, I'd like to select a ProductGroup and TypeOfProduct and then it's pretty clear what the Client doesn't buy.

              I hope, I have described it clear enough.

              Thank you in advance!

                • Re: Show missing value items only
                  pradosh thakur

                  HI

                   

                  Happy new Year!!

                   

                  You want my help in creating two new fields in script ? if yes than whats the logic for it ?

                    • Re: Show missing value items only
                      Ivo Petrov

                      Hello again,

                      No, I don't need your help for creating two more fields. I failed to describe what I'm trying to do.

                      These two fields exists in the table already. I use'em to reduce the number of shown products, using these two category fields - ProductGroup and TypeOfProduct.

                      Let's imagine that the table holds some automotive data as follows:


                      ProductID / ProductName / ProductGroup / TypeOfProduct

                      01 / Volvo / cars / sport wagon

                      02 / Volvo / truck / heavy truck

                      03/ Audi / cars / sport wagon

                      04 / Audi / cars / SUV

                      ... and so on.

                       

                      What I'm trying to do, but I can't, is to see which products haven't been bought by the selected Client, but in order to reduces to number of shown products, I need to filter the results using the other 2 category fileds, as well.

                       

                      Let's assume that a client ABC has bought the car #04 - Audi / cars / SUV.

                      I'd like to see what else he can buy, but not all the other references. Just those that are made by Audi (ProductName) and are not trucks i.e. cars (ProductGroup). Or all the cars (ProductGroup) which are sport wagon (TypeOfProduct).