dataview,exacttarget,ampscript , DataView in AMPScript


DataView in AMPScript

Question:

Tag: dataview,exacttarget,ampscript

Is it possible to access SendLog and DataView data i.e. _Click via AMPScript to populate email content? Something like:

%%[SET EmailContent = _Click(linkName);]%%

I know how to run SQL query and save this as a Data Extension but I'd like to use it on the fly. Does anyone know how this can be done?

Thanks

Dan


Answer:

Yes, it's possible using one of the AMPScript lookup functions. Here's a simple example:

%%[
var @DEColumn1, @lookupValue
set @lookupValue = "whee"
set @DEColumn1 = Lookup("DataExtensionName", "ReturnColumn", "LookupColumn", @lookupValue)

]%%
DEColumn1 is %%=v(@DEColumn1)=%%

I have a few more examples of the different lookup types are here on my blog.

UPDATE: Example retrieving _Click data:

%%[
var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i

set @lookupValue = "[email protected]"
set @numRowsToReturn = 0 /* 0 means all */
set @rows = LookupOrderedRows("_Click",@numRowsToReturn,"EventDate desc","SubscriberKey", @lookupValue)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

for @i = 1 to @rowCount do

var @jobID, @batchID
set @row = row(@rows,@i) /*get row based on loop counter */
set @jobID= field(@row,"jobID")
set @batchID= field(@row,"batchID")

]%%

Row %%=v(@i)=%%, jobID: %%=v(@jobID)=%%, batchID: %%=v(@batchID)=%%<br>

%%[

next @i ]%%

%%[ else ]%%

No rows found

%%[ endif ]%%

Reference: System data views


Related:


how to display dates from two different tables?


c#,mysql,visual-studio,dataview
In a DataView I am displaying two dates from two different tables in a database. Service date from maintenance table and Checklist date from fleetchecklist table. Both columns are called Date in the database tables. The problem is its only displaying one date (checklist date). Service date should say 14/06/2015....

C# Filter DataTable with multiple keys (columns) and multiple values


c#,datatable,dataview
I have a table with three columns A, B and C, where A, B are the keys and C is the corresponding value. I've trying to filter it with multiple values for the keys. In order to do that, I've tried: // Initialise some data DataTable dt = new DataTable();...

Avoid repeating columns in DataTable?


c#,datatable,dataview
This is my DataTable: ItemPartNumber VendorName Price ANNF213 SAMSUNG 265.41 GDFF31D HP 65.10 ANNF213 APPLE 115.51 FSF2122 MICROSOFT 655.47 GDSGG32 NOKIA 250.58 ANNF213 SAMSUNG 225.40 It has ANNF213 three times. I want to take only the first one, and wish to omit the rest rows. Desired Output DataTable: ItemPartNumber VendorName...

Cannot perform = operation between int32 and string. datarow filter


c#,sql,dataview
First of all I apologies for the paraphrasing I will be doing, I do not have access to my code right now and am on my phone but wanted to get this posted so that when I do have access later I hopefully have an answer. If i don't I...

How to Filter a DataView by another datatable row attribute


c#,datatable,dataview
I have 2 methods that I do not have any control over. One returns me all dates with needed unique code(one of the row column row[code]) as DataView. DataView allDatesWithCode= portal.GetAllDatesWithCode(); And another method returns me only available dates but without the code. DataTable availableDates = portal.GetAvailableDates(); Both allDatesWithCode and...

DataView in AMPScript


dataview,exacttarget,ampscript
Is it possible to access SendLog and DataView data i.e. _Click via AMPScript to populate email content? Something like: %%[SET EmailContent = _Click(linkName);]%% I know how to run SQL query and save this as a Data Extension but I'd like to use it on the fly. Does anyone know how...

dataview.rowfilter syntax error - missing operand mystery


vb.net,datagridview,datatable,dataview,rowfilter
Weird, weird error. I might just be codeblind, but: Dim dtdv As New DataView dtdv = dt.DefaultView dtdv.RowFilter = "Controller ID = 'PS2USB1'" XMLDGV.DataSource = dtdv produces, at line 3: Missing operand after 'ID' operator. dt defined and correct. Column heading correct. Weird. Any ideas?...

SQL Duplicate rows


sql,salesforce,crm,exacttarget
I am writing a SQL query for my CRM. My table looks like below: EML SubKey ---------- A A A 1 B B C 2 I want to select unique emails and duplicate emails with non-numeric subkeys. The result should look like A A B B C 2 Please help...

Add Column to DataTable before exporting to Excel file


vb.net,excel,datatable,type-conversion,dataview
I have a DataTable that is built like this: Dim dt As New DataTable() dt = SqlHelper.ExecuteDataset(connString, "storedProcedure", Session("Member"), DBNull.Value, DBNull.Value).Tables(0) Then it is converted to a DataView and exported to Excel like this: Dim dv As New DataView() dv = dt.DefaultView Export.CreateExcelFile(dv, strFileName) I want to add another column...

NoClassDefFoundError: com.exacttarget.etpushsdk.ETLocationManager$RegionMonitor


android,gradle,exacttarget
I'm receiving a weird problem and I don't know if is a Android related or SDK related. The thing is, I'm following the instructions to add ExactTarget Journey Builder SDK (https://github.com/ExactTarget/JB4A-SDK-Android/) , instructions from here: https://code.exacttarget.com/apis-sdks/journey-builder-for-apps/sdk-implementation/register-your-devices.html BUT, when I enabled the LocationService, I'm receiving this weird error: java.lang.RuntimeException: An error...

How to create a DataView RowFilter that can filter if the input string is a substring of the string in the desired column


c#,regex,string,dataview,rowfilter
I would like to create a DataView RowFilter that can filter if the input string is a substring of the string in the column. For example, if the datatable dt has the column "Name", with the string values "Mary" in one row and "John" in the other. I want to...

Need to collect form data on one page, go to a new php page for validation, then finally submit to another URL


php,forms,submit,exacttarget
I am working with ExactTarget's WebCollect and because of this, I have to submit my form data to their URL. Because it's going to their URL, I can't manipulate the data once the form is submitted. What I want to do so that I have more control is first submit...