Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Best aproach for extracting ids from string

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:

2.jpg

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..

1.jpg

app: company id

app_name: company name

Thank you,

Juan.

1 Solution

Accepted Solutions
Not applicable

Re: Best aproach for extracting ids from string

I finally got it

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

Thank you Mayil and qlik_techie,

6 Replies
Not applicable

Re: Best aproach for extracting ids from string

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

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

Not applicable

Re: Best aproach for extracting ids from string

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?

Not applicable

Re: Best aproach for extracting ids from string

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

Not applicable

Re: Best aproach for extracting ids from string

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:

3.jpg

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

thank you.

Re: Best aproach for extracting ids from string

HI

keepchar(FieldName,1234567890) will exclude the strings

Not applicable

Re: Best aproach for extracting ids from string

I finally got it

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

Thank you Mayil and qlik_techie,

Community Browser