InStr textual search and replace within load script

[#1]

Hi,
I am importing data to Qlikview and 1 of the tables contains a field with info relating to the type of internet browser being used and I need to filter these out to be the specific type eg MSIE6.0, Opera, Chrome, Firefox etc. In Access or most other apps I've used Instr and Replace to enable me to achieve this.
Does anyone have any suggestions, else I guess I'll have to amend the data pre loading?
Cheers...............

[deepakk]

Replace function might help you....Check in help for more info..

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

I would combine a mapping table and some string replacements to get what you want. I'm assuming you have something like

PossibleBrowserMap:MAPPING LOAD String, Description FROM[http://www.user-agents.org/](html, codepage is 1252, embedded labels, table is @8); Data:LOAD Date, Time, URL, Agent, ApplyMap('PossibleBrowserMap', Agent, 'Not Listed') AS BrowserFamilyFROM File.qvd(qvd);

That url is one I use to get all possible values for a given browser and I take advantage of the fact that QlikView also read from plan html tables providing a URL, but you can use your own table or even an inline table where you set the values manually.

Hope that helps.

[#4]

Thanks for the idea Miguel as I think I may be able to map; although I've got >1400 variants thus far in my data - as it is the full 'User Agent' string info, so it contains up to 15 subfields!

Eg - Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.1.7) Gecko/yyyymmdd Firefox/3.5.7

Annoyingly, if everyone followed a standard I could use Qlikview's subfield function to pick the browser from subfield 2 [95% of my 1400 is within that, no guessing who's browser that is!!]

Anyway, thanks for the idea................I will verify if I get it working

[Miguel_Angel_Baeyens]

Hi Jon,

It depends widely also in the level of depth you want to get. Simplifying the issue, in your load script you may use something like (untested)

Pick(WildMatch([User Agent], '*MSIE*', '*Firefox*', '*Chrome*'), 'Internet Explorer', 'Firefox', 'Chrome') AS BrowserFamily

where [User Agent] is the field that stores the complete string.

Regards.

[#6]

Hi,

This is a tough one. Did you noticed Internet Explorer still pretends to be "Mozilla" ?

This is a list of all variantions only for Internet Explorer http://www.useragentstring.com/pages/Internet%20Explorer/

You might want a mapping table with all the relevant useragents instead of a long list of regular expressions.

-Alex

[#7]

Hi Miguel,
What a day! Tried the mapping route......................got no hits! I'm presuming it's something to do with field types as the raw data field is a memo type whereas my mapping table is a text field; so I need to re-visit that idea.
As for the wild match, I do need the granularity of which version of MSIE is in use but I can add the variants as required to the script you provided. However, each time I've run the code so far [having taken out 'Internet Explorer'] I seem to only get Firefox returned. Anyway, I will keep trying I guess!

Cheers,

[Miguel_Angel_Baeyens]

JonB wrote:I'm presuming it's something to do with field types as the raw data field is a memo type whereas my mapping table is a text field; so I need to re-visit that idea.

QlikView doesn't understand about data types, it's only text or numeric, and in any case, you have some functions to force or convert, regardless what you have in your datasource. In your case, both memo and mapping should be text, so comparisons should return the right values.

But note that QlikView is case sensitive for both values and fieldnames, so comparing means having the same string in both sides.

Anyway, feel free to upload some sample records in case you don't find the solution so we can check your data.

Regards

[#9]

Hi Miguel,
That's exactly what I thought, but the 1st time I ran the mapping load I got absolutely no 'hits' However, after I re-jigged the raw data output to text and re-ran the mapping load, I got a full set of results which I have been able to verify as correct!
So, thanks very much for your help on this.
I guess as a relative newbie to Qlikview it will teach me to think a bit more outside the box; and less in terms of applications that I have previously used & am well versed in eg Access & Excel.

[Miguel_Angel_Baeyens]

Glad to be helpful!

It takes some time to think out of the box as you say, since QlikView it's not only an extraction tool. This forum and the Reference Manual you have already installed are both valuable sources of information and help, or just ideas of what others did in a situation similiar to yours.

Regards.