topic Re: Best aproach for extracting ids from string in QlikView

Best aproach for extracting ids from string

Thu, 06 Dec 2012 03:09:02 GMT

Hi everyone,

I have a mass mailer program installed in my server which creates metrics from the emails delivered. This data is stored in a data base, but I encountered a problem to extract the ids from the users that opened the email. Each campaign I send, stores the ids of the users that opens the email in a row using the following format:

One row for each campaign..

example: 6130:CO

6130 is the id for the user and CO is the country

I want to be able to select the campaign title and to see which users opened that campaign. This is the layout..

app: company id

app_name: company name

Thank you,

Juan.

Re: Best aproach for extracting ids from string

Thu, 06 Dec 2012 03:26:29 GMT

id for the user : SubField(fieldname,':',1)

id for the company: SubField(fieldname,':',2)

Re: Best aproach for extracting ids from string

Fri, 07 Dec 2012 02:04:36 GMT

I am new using qlikview..

I am accesing those tables from my server using a SQL connection via script..

SQL SELECT app,

title,

opens

FROM database.campaigns;

If I try that syntaxis I get some errors..do I have to consider something different when connecting directly to the db to make the subfields?

Re: Best aproach for extracting ids from string

Fri, 07 Dec 2012 05:27:13 GMT

You've two options to make that work.

1) Create qvds from your SQL datasource & apply the function while you load data from the qvd.

2) Use Load Statement before the Select statement from the SQL and use the function in the Load part of the script.

For eg.

Load *, SubField(fieldname,':',1) as userid,SubField(fieldname,':',2) as Country;

SQL Select * from Database.tablename;

where fieldname is ofcourse the name of the field which contains ur string from which u like to extract the id & country.

Regards

Re: Best aproach for extracting ids from string

Fri, 07 Dec 2012 18:08:24 GMT

Hi qlik_techie,

I am almost there..

if I use Load *, SubField(fieldname,':',1) as userid,SubField(fieldname,':',2) as Country;

I am only getting 3 rows of results(don't know why) and there's a lot more.

So I used SubField(SubField(fieldname,','),':') as idmails; and the results where all separated in the same table like this:

Is there a way to exclude text strings(country code) from the list?

thank you.

Re: Best aproach for extracting ids from string

MayilVahanan — Fri, 07 Dec 2012 18:15:20 GMT

keepchar(FieldName,1234567890) will exclude the strings

Re: Best aproach for extracting ids from string

Fri, 07 Dec 2012 18:27:31 GMT

I finally got it

subfield(keepchar(opens,',0123456789'),',') as [idcorreo];

Thank you Mayil and qlik_techie,