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.

4 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) 

Leave a Reply