Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I finally got it
subfield(keepchar(opens,',0123456789'),',') as [idcorreo];
Thank you Mayil and qlik_techie,
id for the user : SubField(fieldname,':',1)
id for the company: SubField(fieldname,':',2)
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?
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
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.
HI
keepchar(FieldName,1234567890) will exclude the strings
I finally got it
subfield(keepchar(opens,',0123456789'),',') as [idcorreo];
Thank you Mayil and qlik_techie,