Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

I finally got it

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

Thank you Mayil and qlik_techie,

View solution in original post

6 Replies
Not applicable
Author

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

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

Not applicable
Author

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
Author

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
Author

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.

MayilVahanan

HI

keepchar(FieldName,1234567890) will exclude the strings

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

I finally got it

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

Thank you Mayil and qlik_techie,