dataview,exacttarget,ampscript , DataView in AMPScript
DataView in 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?
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
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....
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();...
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...
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...
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...
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...
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?...
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...
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...
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...
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...
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...