-
join two tables with date comparison in where clause MySQL
I have two tables that I need to join based on the date differences. Table a looks like this: ``` Date Partner Revenue 12/01/20 Cosmo 10 12/01/20 Esquire 5 ``` Table b looks like this: ``` Date Partner Revenue 12/01/20 Cosmo 10 12/01/20 Esquire 5 12/02/20 Cosmo 20 12/02/20 Esquire 25 ``` I need to grab everything from…
-
How to include nested folders in S3 advanced connector
Hello. I am trying to set up the connector that would fetch the data daily from S3 bucket. I am working on an advanced s3 connector as it seems to have a more intuitive way to grab the data. My S3 schema looks like this: `s3://adtech-third-party-data/AmazonA9/2021/09/` In DOMO settings credentials, I put…
-
use fields from different tables group by date to calculate percentage
Hi, I am trying to calculate a percentage using two fields from two different tables in SQL Magic Transformation. Table a looks like this: Date All Revenue 09/01/21 100 09/02/21 200 Table b looks like this: Date HDM Revenue 09/01/21 90 09/02/21 170 I need to build a table that would calculate the percentage of HDM Revenue,…
-
Assembler Upsert Date Column
Hi. I need to take a snapshot of today's data in order to compare it with tomorrow's data once it will be available. The problem is that the SQL dataflow and Magic ETL only can use Replace method. I need to find a way how to append the data each day to the already existing historical table. I created Assembler and I chose…
-
Assembler Upsert Date Column
Hi. I need to get a snapshot of today's data in order to compare it with tomorrow's data once we have it. The existed dataflow uses the Replace method, so by just grabbing yesterday's numbers I won't be able to track the changes. So I created an Assembler that would grab the historical data and append today's data each…
-
access max.date from sql table
Hi. I have two input datasets in SQL Magic Transformation. One table called daily the other one estimated . Both tables have the same columns. I need to fetch the data from estimated table but only for the date that is later than max(`Date`) in table dail Data looks like this. Table daily Date Name Revenue 2021-08-01 A 10…
-
How to add _BATCH_LAST_RUN_ to the dataflow built with SQL
Hello, I am trying to find a way to add BATCH_LAST_RUN column to one of the datasets I have created. I found A way - s to change replace method to append. In this particular dataset, I am grabbing the last 7 days' worth of data, so I need to replace the whole dataset on each run. Is there any way to add this column with…
-
Extract Y-M-D from BATCH_LAST_RUN
Hi, I need to extract the year-month-day from the timezone column. The column BATCH_LAST_RUN has values like: `2021-06-24 07:15:04 AM UTC`, what I need is to convert the date to est zone and just have the values like: 2021-06-24 I tried: DATE_FORMAT(MAX(DATE_SUB(`_BATCH_LAST_RUN_`)), '%Y-%m-%d ')) Also, tried to apply…
-
How to grab the last x days in MySQL Table
Hi. I have a very simple task of grabbing the data for the past 7 days. My current query looks like this: select * from `table` where `date` > CURDATE() -7 This works perfectly well, only when we get to the next month it is not grabbing any day from the previous month. I need to grab the data regardless of what month it…
-
quarterly difference in sum(Revenue) saved in a new column SQL Magic Transform
I need to add a new column to my table that will have the difference between sum of revenue of a new quarter over the last one. I am using SQL Magic Transformation. My data looks like this: ``` Website Year Quarter Revenue cosmo.com 2019 4 10 cosmo.com 2020 1 15 cosmo.com 2020 2 5 fashion.com 2019 4 10 fashion.com 2020 1 5…
-
Connect a valid source Error in Magic ETL with BigQuery Service Connector
Hi, I am trying to transfer the data from one dataset to another. The original dataset is getting the data through BigQuery Service Connector. When creating a Magic ETL and choosing my original dataset as `input dataset` and `Append Rows` I get an error: `Connect a valid source to this action to get started`. Not sure why…
-
calculating variance with mysql week over week
I need to display week over week difference with mysql in `Week Over Week Users` column. My data looks like the following: ``` Date Users Week Over Week Users 06-01-2019 10 10 06-08-2019 15 15 06-15-2019 5 5 ``` Currently, `Week Over Week Users` only reflects the data that I have in `Users` column. The desired output would…
-
BigQuery connector
Hi. I have connected BigQuery to DOMO using an access key in JSON format. It connected successfully, however, it only sees one project and not the other two. I have created an access key under the main Project 'A'. In this project, lives other sub-projects -> datasets -> tables. DOMO only allows me to work with the first…
-
join two dataset on two columns sql domo
I am using MySQL I have two tables that I want to join on both columns. My data looks like the following: The first table represents 2019 Revenue ``` Week Name 2019 Revenue 1 Paul 576356 1 Nick 246564 2 Sam 426547265 ``` And the other table represents 2020 Revenue ``` Week Name 2020 Revenue 1 Paul 554 1 Nick 200 2 Sam 400…
-
Group data by week
Hi. I have a dataset with three columns. Date, Revenue, Advertiser. I need to group the revenue so that it shows weekly. I expect the data looks something like the following: ``` Date Advertiser Revenue Week 1 abc 123 Week 2 abd 124 ``` I tried SQL but seems like I have a syntax error. Any help would be greatly…
-
Alerts for dataflows when fails to upload
Hello, I would like to set up the Alerts for all of my dataflows for when it fails to upload. I am a Mac user and only found the solution through Workbench, which is not available for me. How would I set up the alerts in this case? Thanks for the help, Sonya
-
DataSet via Email Connector with the link that goes to zip file
Hi, I am trying to set up an email connector. Every morning I receive a link from the email address and it should go directly to DOMO dataflow. It used to be a simple csv attachment and it used to work but now that it is a URL link that downloads the zip file, DOMO doesn't recognize it. I chose "Download directly from link…
-
Configure tab is missing in preview dataflow
Hi, I am trying to run SQL query on the dataset that has 17M rows. In dataflow that I created, I selected an input data but it only shows 500k rows. After some google foo, I figured that I have to run "Entire DataSet" in the Configure tab in the "Preview of X dataset". However, the Configure tab is missing. How do I filter…
-
filtering the date using sql
Hi, I have a large dataset that I am trying to filter by date in such way that I only have October month, should be straightforward but somehow it doesn't recognize the date at all. Here is my sql code: SELECT `earnings`, `date` FROM `amazon_a9` WHERE `date` BETWEEN "Oct 1, 2019" AND "Oct 31, 2019" GROUP BY `date` I have…
-
Group by date using magic ETL
Hi, I am using Magic ETL to group my data by date. There are only 2 columns in my table: date and revenue. I want to group the revenue sum by date. Is it possible to do so in DOMO? Thank you!
