Best Of
Re: SQL View of DataSets
@EWold It depends on where your dataset comes from. If the dataset is an output of a dataflow, you should be able to see the query in the dataflow editor if it is from a MySQL or Redshift dataflow. Otherwise, I don't believe there is a way to produce the SQL query from a MagicETL.
Re: Calculated Fields - Pipedrive ID's
You would want to use the formula tile in Magic ETL to do this type of conversion. If your list is quite long, another option would be to create a lookup table with a Domo Webform or a spreadsheet that has your IDs and the name you want them to be changed to. You would then use the Join tile in Magic ETL and join your main dataset with your lookup table. This allows you to updated the lookup table if new IDs are added or changed without editing your ETL.
Re: group by and sorting by date
Depending on how your data is organized, you should be able to do this within the card itself. If you use the date as a column/axis, then you can just do sums of all the columns you add and Domo will do all the grouping for you. This will also let you do all the custom filtering and timeframes you want.
Re: Can this card be built without pre aggregating the data
I think you can do this within a card by creating a beast mode using the DAYNAME() function with your date column that you can use as a column or your x-axis. Then the average for each day of the week would be another beast mode:
SUM('amount_column')/COUNT(DISTINCT DATE('datetime'))
Re: Reverting Back to the old dataSet version
Doesn't work, it only reverts changes on the ETL not the dataset:
Reverting Back to the old dataSet version
Hi team,
I changed a few things in my recursive ETL dataset and lost all the rows of data that were recursed in it. When I try to revert the flow to a previous version, it does not show the lost data rows. Any way to retrieve those?
Re: How do you rename null values in a date column?
Hi @emilybaeli -
It sounded like you were wanting to filter your cards to be able to remove those rows with a null value in your date field. I would consider creating a new field (either in the dataflow or via a beastmode).
nullDateFilter:
CASE WHEN `dt` IS NULL THEN 'Missing Date' ELSE 'Contains Date' END
This would allow you to then place the nullDateFilter field into the filter section of your card properties and remove any data that is missing the date.
Re: How to show a specific date with an aggregate (max)
Once you have your sales grouped by Employee and Date, you could also use a rank and window function.
You would want to rank the Sales $s in descending order and partition the data by Employee.
Then, in your visualization, if you only wanted to show the best day for each employee, you would filter the data to only include values where "rank" = 1
Re: How do you rename null values in a date column?
You can use a beast mode in analyzer or a formula tile in Magic ETL 2 and the COALESCE function to change NULL values to another value:
COALESCE(`dt`, '2022-02-02')
