Skip to Content
Author's profile photo Koen Hesters

How to create a search engine in WebI

 

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

Assigned tags

      48 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mahboob Mohammed
      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

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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)))

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog Post Author

      Thanks, but Hey if you have an other solution please share

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed

      Hi Koen,

      My solution would've been almost similar. I answered this question, didn't add in the match1 & match2 feature though!!

      Thanks,
      Mahboob Mohammed

      Author's profile photo Former Member
      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.

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog Post Author

       

      Hi,

      what is the error you get ?

      what happens if you just test variable : Replace([Lookup];[Match 2];”XXX”)=[Lookup]

      Grtz

      Koen

      Author's profile photo Former Member
      Former Member

      Hi. I really appreciate for your response. You can find error screen below

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog Post Author

       

      Hi,

      try

      =Replace([Lookup];[Match 2];"XXX")

      but replace the variable names with your own variable names

      grtz

      Koen

      Author's profile photo Former Member
      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.

       

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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 ?

      Author's profile photo Former Member
      Former Member

      Hi. I have removed the filters. Here is my latest screen and view of variables

       

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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"

      Author's profile photo Former Member
      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.

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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

      Author's profile photo Former Member
      Former Member

      what argument should be written?

      Author's profile photo Former Member
      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.

      Author's profile photo Former Member
      Former Member

      But when I change "XXX" as " " in the match3 variable nothing happens in total. Search  doesn't return anything.

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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"

       

      Author's profile photo Former Member
      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.

       

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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

      Author's profile photo Former Member
      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.

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog Post Author

       

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

      make sure search- variable is in the table as well

      Author's profile photo Former Member
      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

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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

      Author's profile photo Former Member
      Former Member

      The result isn't change. Typing just numbers gives error, number and letter mix returns right result

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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”)

      Author's profile photo Former Member
      Former Member

       

      now the table totally disappears 🙂

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog Post Author

       

      remove all filters, if that doesn't work, just create new table with match2 and search2 and lookup

      Author's profile photo Former Member
      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

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog Post Author

       

      O you changed the input on search 2, you have to keep it on search

      Author's profile photo Former Member
      Former Member

      Hi. I have changed input on search. Result is the same. Perfectly works with letters, but not with numbers.

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog Post Author

       

      what happens if you type "20" in stead of "7459"

      Author's profile photo Former Member
      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. 

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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]))))

      Author's profile photo Former Member
      Former Member

      Hi. Search2 and match2 turns to error

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog Post Author

       

      okay, undo please

      and try typing 74.591 or 74,591

      does work like that ?

      Author's profile photo Former Member
      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.

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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.

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog Post Author

       

      Author's profile photo Former Member
      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.

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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

      Author's profile photo Former Member
      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?

      Author's profile photo Koen Hesters
      Koen Hesters
      Blog 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
      Author's profile photo Former Member
      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.

      Author's profile photo Brenda Ivett
      Brenda Ivett

       

      Hi Koen,

      I tried your solution and it worked wonderfully, however, our end user would like the Input Control to have "Whole Document" Dependency, and when I do that, and switch tabs, even though the filter is applied to both reports, the box of the input control stops displaying the value entered.

      Would you know what I can do for the box to keep the value when switching tabs?

       

      Thank you in advance!

       

      Brenda

      Author's profile photo Naveed Akhtar
      Naveed Akhtar

      Hi Koen,

       

      All search criteria is working fine except ” how to reset the table values after search”?

      Is there any chance to reset the original data set after each search? I'm searching based on on object.

      Thank you very much!

      Nav

      Author's profile photo Ravi Pai
      Ravi Pai

      Input Control > Reset