Best Of
Re: Creating a DataBase on Magic ETL
@luizalauretti You should be able to create the second table using a Group By tile in MagicETL that groups by PPG, RE, and TYPE then takes the Sum of VALUE. Then you can use an Add Constants tile to create a new AREA column with a value of "SP".
Re: Rank Function Skipping Rank Numbers
@lb1234 Try using DENSE_RANK() instead of RANK() to make sure numbers don't get skipped
Re: 3 month moving with current month for a date column
@gbrown You will need to use a Window function for this calculation, although those functions are not supported in Domo's version of MySQL. You would have to use either the Rank & Window ETL tile or write it in a beast mode.
Re: How can I format the date in a YOY chart
@damen You can configure the date format on the X-Axis under Category Scale (X) > Date Output Format
Re: Splitting up multiple-value fields into multiple rows
@jrtomici I did something similar where I break up words into individual values and then put them on each row. I walk through how to do it in this video. Hopefully this will help you.
Re: Row Count growth change
@renee12345 Here is an example that I believe will work for you. I changed two things. I changed the DAY() function to Date(). The DAY() function returns the numerical day of month (i.e. 15 for Dec 15th, 2022) and Date() will return the actual date (i.e. 2022-12-15). Another change I made was that I added in date_add to ensure it was pulling in yesterday's date. This wasn't necessary as curdate()-1 will also work here as @MichelleH pointed out.
(SUM(CASE WHEN DATE(`Snapshot Date`) = CURDATE() THEN `Row Count` ELSE 0 END)
-
SUM(CASE WHEN DATE(`Snapshot Date`) = DATE_ADD(CURDATE(), interval -1 day) THEN `Row Count` ELSE 0 END))
/
SUM(CASE WHEN DATE(`Snapshot Date`) = DATE_ADD(CURDATE(), interval -1 day) THEN `Row Count` ELSE 0 END)
McSQL
Re: Identifying "Last Week' when current week is 0 or 1
CASE WHEN `Date` + INTERVAL (7-DAYOFWEEK(`Date`)) DAY = CURRENT_DATE() - INTERVAL DAYOFWEEK(CURRENT_DATE()) DAY THEN 'Last Week' ELSE 'Not Last Week' END
To add some additional context I'm adding a specific number of days to the date field to get to saturday (7) of that day's week then comparing it to last saturday (current_date - whatever day of the week it is to get back to 7). DAYOFWEEK returns 1-7 depending on which day the date actually is.
So for example since today is Friday DAYOFWEEK is 6. 6 days ago would be the saturday of last week.
Re: Multiple Case When Statements
You were close. Just a couple syntax changes. Change the OR to an IN statement. You also had a second CASE in there that shouldn't be there. This should work for you:
CASE
WHEN `Owner.Name` IN ('Name 1','Name 2','Name 3')
THEN `Owner.Name`
WHEN `Owner.Name` IN ('Name 4','Name 5','Name 6','Name 7')
THEN 'CSG'
ELSE 'No Current Sales Rep'
END
Re: ETL sometimes does bring information and in others it does not
@Rsandoval Does your output dataset actually contain 0 rows or does the dataflow history just show 0 rows updated? Depending on the schedule settings of your dataflow and Quickbase connector, it's possible that no rows were updated but the existing rows still exist in the output dataset.
If a dataflow is triggered and the input datasets have not updated since the last run, then MagicETL v2 will not reprocess the data because the results would be the same. In this case, the dataflow history will show the number of rows changed rather than the total number of rows in your output dataset.