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 mhmohammed 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
- create variable "search" as a dimension: ="..."
- setup an input control entry field based upon "search"
- 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]
- create variable "Match 1" as a dimension =Substr([Search];0;(Pos([Search];" ")-1))
- create variable "Match 2 as a dimension =LeftTrim(RightTrim(Substr([Search];Pos([Search];" ");99999999)))
- 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"
- Add the lookup and like dimensions to your resulttable and hide both of them (for testing you can leave as show)
- 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