New answers tagged

4

Date data types in SFMC are DateTime (or DateTime2) data types behind the scenes, so the time is always included. It's best to use AMPscript to format these, as you can't dictate the format stored in the data extension unless you store them as Text. I generally just use the formatDate() function in AMPscript: %%[ var @date_field set @date_field = ...


0

Is there any particular reason you need the Device ID and System Token in the Data Extension? When sending to a DE in MobilePush, you only need the Contact Key in that Data Extension. The System Token and Device ID fields live on the MobilePush Demographics table, as long as you send to a Contact Key, Mobile Push will know where to fetch those values from.


0

depending on how I interpret your question (and the data extensions look like), I see two scenarios: 1) you use an INNER JOIN or 2) you first break up the first DE and then use a UNION. However if you struggle with these kind of JOINS you may want to look a a drag-and-drop solution like DESelect. We have been using it to avoid having to write SQL and it ...


0

This could work, I've assumed the organization__c field is what you want to appear once and would like to pick the newest lastmodified date for that particular record. The data is split on organization__c and ordered by last modified date and then the newest/most recent row of each organization__c is retrieved. SELECT x.CreatedById, x.LastModifiedById, ...


1

You can retrieve data from the _PushAddress DE (using SQL or Ampscript of course). It contains all of the mobilePush attributes, including the deviceId. for more information regarding the attributes themselves, check this topic: Attributes in _PushAddress and _PushTag Data Views Hope I could help.


1

try like this query SELECT Name, Email, DateTime FROM ( SELECT Name, Email, DateTime, ROW_NUMBER() OVER(PARTITION BY Email ORDER BY DateTime DESC) AS RowNumber FROM Unique_data_AM_01) t1 WHERE t1.RowNumber = 1


5

The naming of your fields suggests that PKey should be your primary key, which cannot be empty in the first place, if you set up the data extension correctly. So therefore you need to either add this functionality to the query that initially fills your data extension, or set it as a default value in the data extension setup. For the SQL solution you ...


0

So assuming that you have your email address as your primary key this is what I would do: SELECT OrderHeader_EmailAddress FROM cust_purch_12mo WHERE OrderHeader_EmailAddress IN ( SELECT SubscriberKey FROM _Open ) You mentioned that you wanted to measure engagement, that's why I used the open instead of click data view. You don't need the where ...


5

I don't think it's doable with union using one query. I would create a staging Data Extension that would store records from your union, and then ROW_NUMBER in nested SELECT to get only newest record for each email. You should use ROW_NUMBER function to arrange record according to your needs and select just first record in list with RowNumber = 1 SELECT t1....


0

What you need to do is use max() and group by. SELECT FieldA, FieldB, max(FieldC) FROM DE1 GROUP BY FieldA UNION ALL [...] https://stackoverflow.com/questions/33921629/select-distinct-with-max-using-sql-server note that: UNION ALL will include duplicates across both your DEs, so if both have the same record in them, you'd have to control for that too. ...


2

Depending on what other fields you need apart from Job ID and Journey ID, you can play around with the following: SELECT s.JobID, j.VersionID FROM _Sent s INNER JOIN _JourneyActivity j ON j.JourneyActivityObjectID = s.TriggererSendDefinitionObjectID


4

If you just like to have the subscribers that haven't opened (or received) an email in the last 90 days you can use a simple SQL utilizing the "Subscribers" and "Open" data views. However, if you just want to select the subscribers that did receive an email, but didn't open it, you need to also use the "Sent" data view. An example that should lead you in ...


2

The issue is that you are comparing apples to oranges in your query. When using DW as the formatting in DatePart, you are returned a number between 1 and 7. 1 is Sunday and 7 is Saturday. So in order to find the day, you will need to do the comparison on the number and then translate the value saved from there. For example: select o.SubscriberKey, o....


0

You could try the following, given the Contact_Key in Organizations DE is equal to SubscriberKey (if it's not, how do they relate?): SELECT s.SubscriberKey, s.EmailAddress AS Email_Address, b.EventDate, j.EmailID, b.BounceCategory, b.SMTPBounceReason, j.Event_ID AS EventID, d.Organisation_Internal_Code, d.Contact_Key FROM _subscribers s LEFT JOIN _bounce ...


1

You would essentially need to join the staging DE an the Target DE to have access to values from both data extensions and then use the SQL Case statement to compare the values. SELECT s.PrimaryKey, CASE WHEN s.Field is null THEN t.Field ELSE s.Field END as Field FROM StagingDE s JOIN TargetDE t ON s.PrimaryKey = t....


1

Ashish, I'm having a hard time understanding what you actually try to achieve. You say you want to achieve the same as a union statement, but the image you attached does not show that. In fact, I don't see the logic of how DE4 should be filled from that image. I'm going to assume you made a mistake in your image of DE4 and want to get the rows of the 3 ...


0

They are special characters that the DE doesn't recognize. Try using the HTML equivalent (i.e. instead of ® use ®). Reference link


1

As your target data extension has to be the one containing all of the fields in Data Extensions 1-4 (that you like to write to it), you can add the additional fields to your select-statements for data extensions 1-4 using a null value and an alias. Prerequisite: The fields missing in your source data extensions need to be nullable in the target data ...


0

SELECT DeOneColumnName1 as DeFiveColumn1, DeOneColumnName2 as DeFiveColumn2, ..., email FROM DEOne UNION ALL SELECT DeTwoColumnName1 as DeFiveColumn2, DeTwoColumnName2 as DeFiveColumn1, ..., email FROM DETwo Assumption the target dataExtension has only X fields which are named "DeFiveColumn1", "DeFiveColumn2" , ... and email You can use the as parameter ...


5

I would use JOINs to connect these. You would want to decide which values you want (see above chart for a great visualization of SQL Join results) to keep, and that will determine your type of join. For instance, if you want to return all results from matching values in the tables, you would use: SELECT a.FieldA, a.FieldB, b.FieldC, c.FieldD FROM [DE1] a ...


0

You can try using a full outer union: SELECT fieldA, fieldB FROM ( (SELECT fieldA, fieldB FROM De1) 1 FULL OUTER JOIN (SELECT fieldA, fieldB FROM De2) 2 on 1.fieldA=2.fieldA FULL OUTER JOIN (SELECT fieldA, fieldB FROM De3) 3 on 1.fieldA=3.fieldA ) the target should be De4 and assuming your primary ID is fildA


3

You can achieve this by using UNIONS. Unions are a way to combine data of two or more dataextensions and insert the result into the dataextension (or table) of your choice. Here are some nice posts regarding unions: 1 2 Depending on your use case you need to switch between union and union all. Union will perform in a way that you will only get distinct ...


1

This is possible with an Automation containing SQL query activities. You need to select the fields you like to write/update to/in your MasterDe and make sure you have a matching primary key, so the fields can be updated correctly. Things you need to consider: You need to have the field used as primary key in MasterDe present in your other data extensions. ...


1

I don't think you need the ROW_NUMBER function, the issue's with your grouping as p.EventDate is a time stamp (down to the millisecond). You'll get one row returned in your query for the most recent send to each Subscriber, effectively. Try this: SELECT COUNT(1) AS TotalSent, DATENAME(month, p.EventDate) as [MONTH], 'XX' as [Business_Unit], ...


Top 50 recent answers are included