Skip to Content

 

How to create a search engine in WebI using an input control where we can type some tag-words and see the result of our lookup in number of hits and the result in detail.

 

in this example we wanted to know all the prices of the black Bermudas

In this example we wanted to know something about the state with “or” in it and something about Trousers but how do you write “Trousers”, so we filled out something like “ous”

Note:  I did meant to do a search on states with or in, but we see “Illinois” par example in our result.  This happens because we do a search on all the values listed, check out “things to know” to comprehend this, if you want it to work as a filter on a particular dimension, you will need to address it so.

 

This resulted in a lot of lines off course because ous and or combination is not a very specific one!

Well things to know about this searchengine:

  • It is set up for 4 dimensions (Sate, Category, Color, Lines) we concatenated those dimensions in one Variable: Lookup
  • The search input control / entry field can handle  1 or 2 separate strings separated via a space in between, if you want to use * or something else to separate the strings you’ll need to change the formulas in the variables
  • if you use Bermudas or bermudas you will have different results, because I didn’t cleansed my data.  Or check out Mahboob Mohammed comment for the solution
  • If you use “ous or” or “or ous” that will have no effect on the result, because each string is valuated seperatedly

Okay steps to reproduce

  1. create variable “search” as a dimension: =”…”
  2. setup an input control entry field based upon “search”
  3. create variable “lookup” as a dimension =concatenation of your dimensions you want to consider in your search: =[Query 1].[State]+[Query 1].[Category]+[Query 1].[Color]+[Query 1].[Lines]
  4. create variable “Match 1″ as a dimension =Substr([Search];0;(Pos([Search];” “)-1))
  5. create variable “Match 2 as a dimension =LeftTrim(RightTrim(Substr([Search];Pos([Search];” “);99999999)))
  6. create variable “like” as a dimension: =If Replace([Lookup];[Match 2];”XXX”)=[Lookup] Then “no match” ElseIf IsNull([Match 1])= 1 Then “match” ElseIf Replace([Lookup];[Match 1];”XXX”)=[Lookup] Then “nomatch”  Else “match”
  7. Add the lookup and like dimensions to your resulttable and hide both of them (for testing you can leave as show)
  8. filter the resulttable upon the variable “like” set as equal to match

 

Now we are ready to play

I want a wallet in Ecru

 

ADDENDUM

If you want to be able to add more search terms in the input control, you can do it via:

  • Add new variable: Match 3: =RightTrim(LeftTrim(Replace (Upper(Replace (Upper([Search]);Upper([Match 1]);” “));Upper([Match 2]);” “)))
  • Change old variable Match 2 = =Upper(Substr(Upper(RightTrim(LeftTrim(Substr([Search];Length([Match 1]+1);99999999))));0;(Pos(Upper(RightTrim(LeftTrim(Substr([Search];Length([Match 1]+1);99999999))));” “)-1)))
  • Change old variable Like: =If Replace([Lookup];[Match 3];”XXX”)<>[Lookup] And IsNull([Match 2])=1 And IsNull([Match 1])=1 Then “match” ElseIf Replace([Lookup];[Match 3];”XXX”)<>[Lookup] And IsNull([Match 2])=1 And Replace([Lookup];[Match 1];”XXX”)<>[Lookup]  Then “match” ElseIf Replace([Lookup];[Match 3];”XXX”)<>[Lookup] And  Replace([Lookup];[Match 2];”XXX”)<>[Lookup]  And Replace([Lookup];[Match 1];”XXX”)<>[Lookup]  Then “match” Else “nomatch”

PLAY TIME : casual black dress

And fortunally a search with only 2 terms is still doing its thing -> Black Dress

To report this post you need to login first.

45 Comments

You must be Logged on to comment or reply to a post.

  1. Mahboob Mohammed

    Great work Koen!! I was thinking about writing this blog, due to the number of requirements. Suggestion: It’d be nice if you could use UPPER() for the Match1, Match2, and Lookup, so the case of the string searched for shouldn’t matter.

    Thanks,
    Mahboob Mohammed

    (1) 
    1. Koen Hesters Post author

       

      Hi

      according to your suggestion

      LOOKUP=UPPER([Query 1].[State]+[Query 1].[Category]+[Query 1].[Color]+[Query 1].[Lines

      Match 1=UPPER(Substr([Search];0;(Pos([Search];” “)-1)))

      Match 2 =UPPER(LeftTrim(RightTrim(Substr([Search];Pos([Search];” “);99999999)))

      (0) 
  2. Former Member

    Great job Koen. Thank you very much. I would like to use  this feature in my report and added variables step by step. however I met problem in adding variable number 6.Like

    I have tried different syntc instead of “XXX” but none of them were accepted. Please help me with this issue.

    (0) 
  3. Former Member

    Hi.

    I have named all variables as you showed. But forget space 🙂 now variable is created. Thank you again for your response.

    Also, appreciate valuable addition made by Mahboob.

    Now I have another problem. I have created all variables shown by Koen and Mahboob.

    As shown below I would like to perform search by chassis number. When I follow Koen’s instructions my table disappears.

    When I follow Mahboobs directions nothing happens. Search returns no result.

    What should I write in search field in order to find needed chassis number. Writing all 17 or more characters is impossible. Last 5-6 numbers is preferred.

     

    (0) 
  4. Koen Hesters Post author

    Hi,
    remove the filter of your table and include the variables in your result table and set them as show.
    when using your input variable, what does happen now ? are all your variables filled in okay ?

    (0) 
    1. Koen Hesters Post author

       

      Hi,

      okay but can you add match 1 match 2 etc to your table and fill something out in your input control, so we can see what is filled out in those variables

      grtz

      Koen

      (0) 
      1. Koen Hesters Post author

         

        Hi,But if you are going to use the above only for 1 search string you can do it simple by the search variable and match1: =If Replace(Upper([Store name]);Upper([Search]);”XXX”)=Upper([Store name]) Then “no match” Else “match”

        Where storename = chassis nummer

        and you can filter on match 1= “match”

        (0) 
  5. Former Member

    Hi.I have followed the steps as you wrote.

    1) I have added match1 and match2 to my table and fill out my input control. Here is my screen. Match1, match2 and like columns turn to error. And nothing returned as a search result.

    (0) 
    1. Koen Hesters Post author

       

      Hi,

      can you set for “like” variable:

      =If Replace(Upper([Store name]);Upper([Search]);”XXX”)=Upper([Store name])

      replace the [Store name] by your [Chassis] dimension

      grtz

      Koen

      (0) 
    2. Former Member

      2) Regarding to your second direction I have created match3. At first I write Chassis number instead of XXX and set filter match. Then chassis numbers in my table disappeared, I show only false registries, where some wording instead of chassis numbers. I filled out input value with this wordings, like binqo, and it returns right results. But I have lost my true chassis numbers.

      (0) 
        1. Koen Hesters Post author

           

          I’m not quiet following

          but the idea is

          1. create variable “lookup” as a dimension =concatenation of your dimensions you want to consider in your search: -> in your case: Upper( [chassis])
          2. create variable “search” as input control string
          3. create match3: If Replace(lookup;Upper([Search]);”XXX”)=lookup Then “no match” Else “match”

          So what you should have is:

          lookup are your chassinumber

          when search = 232871

          match 3 = replace results in: chassinumber if 232871 is found replace by XXX

          example chasis:

          •  2323ABA3323XXX
          • 2323ABA3323232872

          then we compare the replaced strings with the original one

          •  2323ABA3323XXX        =?  2323ABA3323232871
          • 2323ABA3323232872     =? 2323ABA3323232872

          if it is equal-> no match because the replace function didn’t do anything

          it is not equal -> match because the replace function did replaced the “232871” by “XXX”

           

          (1) 
  6. Former Member

    Hi Koen. I am so sorry for bothering you. I have deleted all previous created variables. And recreate these ones again:

    1. Lookup = Upper( [chassis])
    2. search = “” and added as input control
    3. match = If Replace(lookup;Upper([Search]);”XXX”)=lookup Then “no match” Else “match”

    while adding match variable as a filter only “no match” option appears.

    filling search filed doesn’t return anything.

     

    (0) 
    1. Koen Hesters Post author

       

      you used I hope [lookup], can you share printscreens of a table with all the variables inclusive, make sure you typed something in the input control

      (0) 
  7. Former Member

    Hi. I have tried the same thing in my another report. The result is the same. Here are my variables.  

     

    I have added them to my table. Before typing anything to search field screen is ok.

    when I type something for search match column returns error.

    (0) 
    1. Koen Hesters Post author

       

      hi, can you add column, with formula: Replace(lookup;Upper([Search]);”XXX”)

      make sure search- variable is in the table as well

      (0) 
      1. Former Member

        hi. I add new match variable and search to my table. I found an interesting result. When I type mix search like numbers and letters it returns good result.

        However, when I type just numbers I receive different result

        (1) 
        1. Koen Hesters Post author

           

          Allright now we have something to work on.

          I reckon it is going in error because somewhere he sees a number in stead of a char what throws the replace formula in error.

          could you try this please ?

          =Replace(Concatenation(“M”;[lookup]);Upper([Search]);”XXX”)

          add above in once again in another column, to see what this is doing

          (1) 
            1. Koen Hesters Post author

               

              create new variable: Search2

              =Upper(LeftTrim(Concatenation(”  “;[Search])))

              and

              change the last variable match 2 by

              =Replace(Concatenation(“M”;[lookup]);Upper([Search2]);”XXX”)

              (1) 
                  1. Former Member

                    I don’t have any filters on this table. Anyway, I have removed everything and created new table with match2, search2 and lookup.

                    it is also disappears while filling the search

                    (0) 
                          1. Former Member

                            It returns good result. Also I find another number 10, which is mixed with letters in one row, and within numbers in another row. It also returns good result. Finally i tried problematic row with 0 it also returns good result. 

                            (0) 
                            1. Koen Hesters Post author

                               

                              hi

                              so the issue is the formatting of the number in the first place (thousands fomat)

                              can you change search2 into:

                              =Upper(LeftTrim(Concatenation(”  “;ToNumber([Search]))))

                              (0) 
                                  1. Former Member

                                    none of them is working.

                                    I found one interesting thing: while typing 3 numbers, it returns good result. While typing 4 and more it doesn’t return anything. May it depend on “XXX” – three X in our match function? this is related only to numbers. With letters everything is OK. It doesn’t matter how many character I fill in search, it returns right result.

                                    (0) 
                                    1. Koen Hesters Post author

                                      Hi,

                                      no, it something to do about in one way your report is getting numbers, even if I via the formulas we turned them into char/strings it keeps on getting numbers instead of strings.  I’ve done it on my machine as well and it does work even for numbers, because they do turn to strings on my WEBI BO 4.1 SP7.  In toyr case it is still reacting on numbers, if you do like less then 4 it is okay because you don’t enter the thousand separator, if you do more than 3 it hits I think against the . or , thousand separator in your system.  What you can do is try to get the numbers as string.  The thing is on my system it all works, my initial demo and the search2 and match2 even for numbers, but I can’t reproduce your issue.

                                      (0) 
                                      1. Former Member

                                        Hi. Thanks for sharing your example. I really confused in my variables. Could you please check them once again:

                                        1. search  =””
                                        2. search2=Upper(LeftTrim(Concatenation(” “;[search])))
                                        3. lookup  =Upper([Itemid])
                                        4. match   =Replace(Concatenation(“M”;[lookup]);Upper([search]);”XXX”)
                                        5. match2 =Replace(Concatenation(“M”;[lookup]);Upper([search2]);”XXX”)
                                        6. match3 =If Replace([lookup];Upper([search2]);”XXX”)=[lookup] Then “no match”                   Else “match”

                                        variable 1. is added as input control.

                                        (0) 
                                        1. Koen Hesters Post author

                                           

                                          Hi,

                                          I used

                                          • Search =””
                                          • Search2 =Upper(LeftTrim(Concatenation(”  “;ToNumber([Search]))))
                                          • Lookup  =Upper(“M”+[Itemid])
                                          • Match2   =If Replace([Lookup];Upper([Search2]);”XXX”)=[Lookup] Then “no match”                 Else “match”
                                          • Input control on Search entry field
                                          • Filter on Match2=match

                                          (1) 
  8. Former Member

    hi Koen. Despite I have a problem with numbers in my report, I use your method in my other reports and really appreciate for such a useful blog. Search item in the report is really fantastic and very helpful. Thank you very much.

    I want to ask another question related to the search. I set filter in my report and selected match.

    = If Replace([lookup];Upper([search]);”XXX”)=[lookup] Then “no match” Else “match”

    All table disappears, and only the value filled in the search returnes as a result. Can I show all table by the default, and get result only while filling the search?

    (0) 
    1. Koen Hesters Post author

       

      Hi

      You can use:

      -> check out my other response, I added it and tested it on the eFashion universe

      • Search =””
      • Search2 = Upper(LeftTrim(Concatenation(”  “;If ([Search]=””) Then [Search] Else ToNumber([Search]))))
      • Lookup  =Upper(“M”+[Itemid])
      • Match2   =If [Search]=”” Then “match” Else If Replace([Lookup];Upper([Search2]);”XXX”)=[Lookup] Then “no match”                 Else “match”
      • Input control on Search entry field
      • Filter on Match2=match
      (1) 
      1. Former Member

        Thank you very very very much Koen. You really saved me. I will use this feature almost in all my reports. Hope that problem with the numbers is limited only with one report.

        (0) 

Leave a Reply