Best Of
Re: Filtering by latest date in table
That is a bit interesting as both values should be appearing in your list. Are you grouping your data by anything or are you graphing your table by month instead of by day in the date selector?
As for your ETL you can use the Group By tile with one caveat. It requires you to group by something. You can add a constant value column with a value of 1, group by that column, select the max date then join back to your original dataset on that new constant column (I typically call this column Join Column). I've attached the JSON below which you should be able to copy and paste into your Magic ETL (1.0 or 2.0). If you go this route you can then just compare your max date field and your date field to see if they're the same in a beast mode instead of using the window function. (My date field was called dt - you'll need to change it for your field)
{"contentType":"domo/dataflow-actions","data":[{"name":"Add Constants","id":"45258bf9-400b-41ab-9ae9-a90d60a3fba9","type":"Constant","gui":{"x":180,"y":180},"dependsOn":["59227c5b-3d41-4c8a-a7d0-4fb85b7dd545"],"removeByDefault":false,"notes":[],"fields":[{"name":"Join Column","type":"LONG","value":"1"}]},{"name":"Group By","id":"43b922e3-5a31-4ed5-bb02-b4a04933dd22","type":"GroupBy","gui":{"x":312,"y":228},"dependsOn":["45258bf9-400b-41ab-9ae9-a90d60a3fba9"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Join Column"}],"partitionedAggregation":false,"fields":[{"name":"Max Date","source":"dt","type":"MAX"}]},{"name":"Join Data","id":"63474fc7-ba57-4220-8ea7-2327e81d64fd","type":"MergeJoin","gui":{"x":420,"y":180},"dependsOn":["43b922e3-5a31-4ed5-bb02-b4a04933dd22","45258bf9-400b-41ab-9ae9-a90d60a3fba9"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"43b922e3-5a31-4ed5-bb02-b4a04933dd22","step2":"45258bf9-400b-41ab-9ae9-a90d60a3fba9","keys1":["Join Column"],"keys2":["Join Column"],"schemaModification1":[],"schemaModification2":[],"partitioningInputId":""}]}
Re: Filtering by latest date in table
Glad I could help! If you could make sure to mark it as an accepted answer so others can easily find this I'd appreciate it!
Re: Is it possible to change the connector type from Redshift to Redshift SSH?
Hi @user089739
Because how the connectors work you'll need to rebuild your datasets and reconnect your cards.
Re: Join Issue
LEFT JOIN, RIGHT JOIN and OUTER JOIN will all return NULL values if it can't find a match between your two tables based on your join condition. This website may help explain the differences between the different join types: https://www.w3schools.com/sql/sql_join.asp It's working correctly based on your join columns and condition. What are you expecting to see with your joins?
Re: Join Issue
Hi @Fadem
What exactly is your issue? What are you getting currently and what are you expecting? Which tool are you utilizing to do you join? View? Magic? MySQL?
DataSet Views and Magic ETL allows you to specify the join type in the UI. MySQL you need to specify either LEFT, RIGHT or FULL OUTER before your join to do a non-inner join.
Re: MySQL Recursion Performance/Speed Issues
Hi @etszyingho
I'd recommend utilizing ETL 2.0 if at all possible as it will be more performant than MySQL dataflows but it looks like you're at a good spot with your current performance with @MarkSnodgrass 's solution.
Re: Connect to SFTP - Select ALL files in root directory
Hi @usergru
It's not possible with the SFTP connector however have you thought about utilizing workbench, scheduling an SFTP sync to download files in the folder to a DataSync folder and let workbench handle creating any jobs that are needed? https://domohelp.domo.com/hc/en-us/articles/4407019838487-Configuring-a-DataSync-Folder-in-Workbench-5-1
Re: Connect to SFTP - Select ALL files in root directory
A custom connector may prove to be difficult as it's must be written on ES5 which is an older version. You may be able to hack something together to interact with an SFTP folder, pull all the files and then loop through all of the files but you'll be restricted to what you can utilize. I'd be interested to hear if you're able to get one written and working.
Workbench does run on a machine (virtual or physical) within your own intranet.
Re: How to use a calculated field in a SUM function? (Statistics)
Hi @ramoshe
This is because you're attempting to aggregate (SUM) on an aggregate (AVG) which isn't possible within a beast mode. You'd need to calculate the average of your dataset in your ETL and then use the resulting column in your beast mode. The downside to this is that it won't be respective of any filters you apply to your card.
Re: Time Format
Glad I could help @Liliana. If you could accept my answer so others can find it easier I'd appreciate it! Thanks.