Interrogate URL

[pkelly]

We have built an internal web site and we want to analyse what our users are searching on....

We have the searched URL's stored in a field...examples below...

http://Server/Application/Search.aspx?src=pack&d=false&s=ASHA-KD&v=&

http://Server/Application/Search.aspx?src=sheet&mc=Veneered+MDF&cert=&sc=Ash+-+White&t=&t1=

What I would like to do is...

For src=pack, output the "s=" search - in the example above I would get "ASHA-KD"

For src=sheet, output the "sc=" search - in the example above I would get "Ash - White" (need to also filter out + signs)...

Any help greatly appreciated....

[rwunderlich · Accepted solution]

There are a number of Qv String functions that would be useful, as well as the preceding load. The example offered here allows you to expand to numerous "src=" values which I'm guessing your data may have.

SearchMap:
MAPPING LOAD * INLINE [
Src, Key
pack, s=
sheet, sc=
]
;

LOAD *,
Replace(TextBetween(Url, ApplyMap('SearchMap', Src, ''), '&'), '+', ' ') as Search
;
LOAD *,
TextBetween(Url, 'src=', '&') as Src
;
LOAD * INLINE [
Url
http://Server/Application/Search.aspx?src=pack&d=false&s=ASHA-KD&v=&
http://Server/Application/Search.aspx?src=sheet&mc=Veneered+MDF&cert=&sc=Ash+-+White&t=&t1=
]
;

-Rob

[pkelly]

Thanks Rob...this did the trick...