5 Replies Latest reply: Sep 9, 2011 5:58 AM by Stefan Wühl RSS

    search engine in script

      Hello,

       

      I am new to this community and like to ask a question.

       

      In our database we have a variable with an description of products. What we miss is a variable with Brandname.

      In QV I can use the search option (loupe) to find the associating brands. But I cannot do this for multiple brands or at least I don't know how.

       

      Is it possible to make a script in which I 'automatically' appoint a brand in a new variable 'Brand' from an other variable?

       

      Thanks for your help.

      Ronald

        • search engine in script
          Stefan Wühl

          Welcome, Ronald,

           

          I am not sure if I fully understand your question. Could you post an example of product descriptions and how you want to generate brands out of that (or am I totally off?)?

           

          I don't understand how you can search for brands with the search function in the front end if you don't have the information in the tables. Or are brand names part of the description?

           

          If we understand how you search for brands with the search function, it should/might be possible to generate a new field in the script, too.

           

          Regards,

          Stefan

            • Re: search engine in script

              Dear  Stefan,

               

              In our database we have the product description in the variable ‘art_artikel_oms

               

              Here are some examples in our database and how we like to rearrange into brands.

               

              You notice that e.g. BOSS as well as H.Boss as well as Hugo Boss as well as Hugo must direct to Hugo Boss

               

              And both C.Klein as well as CK must direct to Calvin Klein,

              But HAJENIUS KLEINE TUITKNAP with ‘KLEIN’ in description is NOT Calvin Klein

               

               

              ART_ARTIKEL_OMS

               

              Brand

               

              remark

               

              BOSSBL M belt, 85, dk brown

               

              Hugo Boss

               

              BOSSOL M t-shirt, L, Green

               

              Hugo Boss

               

              Bvlgari Man                    Edtsp 100

               

              Bvlgari

               

              C.Klein CK One                 Edtsp 200

               

              Calvin Klein

               

              C.Klein Eternity Men           Ash   100

               

              Calvin Klein

               

              Chanel Vitalumiere Aqua Spf15 Found BR20

               

              Chanel

               

              CK M bw bottom, L, Black

               

              Calvin Klein

               

              D&G The One Gentleman          Ash   100

               

              D&G

               

              D.Karan DKNY Women             Edpsp  50

               

              Donna Karan DKNY

               

              Davidoff Champion              Deost  70

               

              Davidoff

               

              DAVIDOFF EXQUISITOS 10 ST

               

              Davidoff

               

              Dior Miss Dior Cherie          Edpsp  50

               

              Dior

               

              E.Lauder Pure Color            Lipgl  03

               

              Estee Lauder

               

              G.Armani Code Women            Edpsp  50

               

              Giorgio Armani

               

              Gucci Guilty                   Edtsp  50

               

              Gucci

               

              H.Boss Boss Bottled Night      Ash    50

               

              Hugo Boss

               

              HAJENIUS KLEINE TUITKNAK

               

              Hajenius

               

              '=cigar, no calvin klein

               

              Hugo Boss Femme by Boss        Edpsp  50

               

              Boss

               

              Hugo Element                   Deost  75

               

              Boss

               

              J WALKER BLACK LABEL 40%

               

              Johnny Walker

               

              Kenzo Flower Women             Parsp  50

               

              Kenzo

               

              KLEINBRILJANT

               

              '=diamond, no calvin klein

               

              Lancome Miracle                Edpsp  50

               

              Lancome

               

              L'Oréal Resist&Shine Titanium  Nailp 501

               

              L'Oreal

               

              O'Neill Moya pink 39

               

              O' Neill

               

              Prada LR Metal Brn PS162   PS50M 4AC 6S1

               

              Prada

               

              SOUTH CHARDONNAY ORGANIC

               

              '=wine, no Donna Karan

               

              YSL La Nuit                    Parsp 100

               

              Yves Saint Laurent

               

               

              Can you help me?

               

              Best regards,

              Ronald

               

               

              Van: swuehl qliktech@sgaur.hosted.jivesoftware.com

              Verzonden: dinsdag 23 augustus 2011 21:06

              Aan: Wolfers, Ronald

              Onderwerp: - Re: search engine in script

               

              QlikCommunity<http://community.qlik.com/index.jspa>

               

              Re: search engine in script

              created by swuehl<http://community.qlik.com/people/swuehl> in New to QlikView - View the full discussion<http://community.qlik.com/message/143375#143375

                • Re: search engine in script
                  Stefan Wühl

                  Ronald,

                   

                  I will try:

                   

                  I had a look at your data and noticed that at the beginning of each description ART_ARTIKEL_OMS, there we find the brand identification tag, is this correct?

                   

                  My approach has four steps:

                   

                  1) Build a table with a matching relation between the identification tag and the brand name (I think we can't live without something like that), like

                  ...

                  BOSS, Hugo Boss

                  ...

                  to match both BOSSOL and BOSSBL and link to Hugo Boss.

                   

                  2) Sort this table by descending Tag length

                   

                  3) Read in a ART_ARTIKEL_OMS and for each read record, loop though all matching tags and compare the left part of ART_ARTIKEL_OMS with the matching tag, if these match, we have found a brand. break this loop if all tags are compared or we have found a brand and continue with next ART_ARTIKEL_OMS

                  (Here comes the sorting of the tags into play: If you would have two tags like BOSS and BOSSOL, I would like to compare first the more significant (i.e. longer) tag to the ART_ARTIKEL_OMS, if these match, remember we break the loop).

                   

                  4) Now we have to remove all the rows with no match, load the table again with an where clause.

                   

                  Maybe I missed something more simple, but that's how it should work assuming your ART_ARTIKEL_OMS start with a brand identification. If they don't always do, we might could easily change the comparision to a match() function or similar, but we could run into deep trouble if there are matches that don't indicate a brand (like in some of your example rows (with Donna, Klein tags))

                   

                  See attached sample for more details.

                   

                  Hope this helps,

                  Stefan