How to merge two rows in one?
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.
Comments
-
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.2 -
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.
1 -
are you sure the Str_to_date function in your beast mode is converting both dates to the same date correctly?
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'1 -
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.
1 -
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.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
No, I do not have any sorting at the moment.
0 -
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.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Group By on date, aggregate(sum) for Impressions and clicks and no aggregate on CTR.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive