How to merge two rows in one?

user04645

Hello Everyone,

 

I have 2 datasets using the email connector. These 2 datasets contain information from 2 different platforms (Adform and Adwords), by means of a DataFlow I made a data unification of these two so that they remain in a single data base that I call "Merge Dataset".

 

Now up here, I create some cards with the information of this "Merge Dataset" but for obvious reasons I have duplicate the days for being 2 different platforms and different data of Clicks, Impressions, CTR, Viewable Rate, among other fields, something like this:

 

 

The idea is to create a conditional that evaluates if the dates are equal then do the summation of Clicks & Impressions and that duplicate is eliminated, leaving only a single row with the totals and get something like this as a result:

 

 

In detail, the Fields that are wanted have the following properties:

 

Date - It is a field of String type (For compatibility issues between two datasets, I had to format the date field to be a string).

Clicks and Impressions - These are Whole Number fields.

CTR - It is a field that is obtained from the formula 'Clicks' / 'Impresiones' and a percentage format is applied to the card.

 

I have seen that the form would be to use the Beast Mode inside the cards but I do not have much knowledge of SQL, only of how the conditional would be. To make you a little clearer the idea of ​​the conditional would be something like this:

 

For each element contained in date {

     If ('Date1' == 'Date2') then {

         ClickTotals = Clicks1 + Clicks2;

         ImprTotals = Imprs1 + Imprs2;

     }

}

 

That would be more or less the pseudo-code that I would like to use. Now this is applied to theory, in practice if there is a simpler method in which i can obtain the same results, that information would also be well received.

 

I know that the pseudocode has several faults but it would be the way in which I am seeing it, consider total freedom to make any kind of solution, really, important thing is the results.

 

Valiant

So it should be a bit easier than you may be expecting. First thing, on your date column, it looks like two different formats (from the 2 datasets) are being returned. You may want to do a

 

STR_TO_DATE(`Dia`, '%d/%m/%Y')

to make sure they are converted correctly

 

When building your chart, the date will automatically be combined when you perform an aggregation on the Clicks and Impressions fields.

 

If you were to create a table card you would hvae 4 fields:

Date 

SUM(Clicks)

SUM(Impressions)

SUM(Clicks)/SUM(Impressions)

 

With the other 3 columns being aggregated, as long as your date formats are the same it will combine those multiple records for you.

 

Let me know if you have any other questions,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

user04645

Thank you for your answer, I was testing and with the date format, it works just showing a date without being duplicated. The problem is that he does not show the sum of the clicks and impressions. I'm probably doing something wrong.

 

I put a prints of the before and after the card so you see the changes and maybe you can see the error.

 

This is the card before applying the solution.

 

This is the card once the solution is applied. It has the date field formatted to date and in the fields of clicks and impressions are formatted to SUM.

 

As you can see, they are still the values of only one field and not the sum of both rows. should give as a total: Clicks = 868 & Impressions = 412674

 

Date formatted field.

 

Clicks and impressions fields with the SUM as format.

 

 

I'm sure I'm making some mistake but I have no idea what it could be.

 

 

Godzilla

are you sure the Str_to_date function in your beast mode is converting both dates to the same date correctly?

user04645

Yes, apparently it is showing a single date and not as previously showed 2, one for each platform.

It could also be happening that only one of the 2 platforms is shown, which is what it really looks like.

 

AS

Do you have any sorting in place?  Sometimes having a sort in place that isn't one of the dimensions or measures can break aggregations.

user04645

No, I do not have any sorting at the moment. 

 

AS

Looks like you might have to do two types of formatting in a beast mode to get the two date string variants aligned. 

Something like 

case when `dia` like '%/%' then str_to_date(`dia`,'%d/%m/%Y') else str_to_date(`dia`,'%Y-%m-%d') end

 

That might not be totally correct syntax, but should be something to try.

NFSharma

Group By on date, aggregate(sum) for Impressions and clicks and no aggregate on CTR.