Best Of
Re: Exclude from List
CASE WHEN `Bill_To` LIKE 'PREFIX%' THEN 'Ignore' ELSE 'Include' END
Re: SQL View of DataSets
@RobSomers unfortunately, "The Magic ETL is just a visual representation of SQl" this is not an accurate statement and is potentially misleading.
Although Magic ETL transforms borrow heavily from transforms we are accustomed to performing in SQL and even the Formula Tile allows you to write functions borrowed from MySQL, it is not accurate to assume that the engine underneath Magic ETL is a SQL database.
I believe the functions under the covers are implemented in Java and they are performing operations in more of a streaming text pipeline.
This might be splitting hairs, but it's important to understand because if you think of Magic as a database layer with Tables where you're issuing UPDATE commands against a set of records, it may discourage you from thinking of ways you might try to support Magic as it subdivides your tasks across multiple nodes for parallel processing.
In fact the biggest difference and improvement in Magic over your SQL pipelines (Adrenaline, Redshift, and MySQL) include
1) Magic does not have to load all the data into a table before it can be processing the data. Because the data is moving in a text stream, as first as the first row of data arrives it can start flowing through your ETL tiles.
2) Magic does not have to Index. (this is a double edged sword). Because Magic is not a database engine there is no concept of indexing. therefore JOINs, RANK, and SORT operations would be slower than the equivalent operation performed in Redshift or Adrenaline (assuming all other factors were equal).
Re: Rating Card
You can make stars by doing a beast mode like this:
(CASE
WHEN AVG(Object_Health_Score) >= '.8' THEN ' ★★★★★'
WHEN AVG(Object_Health_Score) >='.6' THEN ' ★★★★☆'
WHEN AVG(Object_Health_Score) >= '.4' THEN ' ★★★☆☆'
WHEN AVG(Object_Health_Score) >= '.2' THEN ' ★★☆☆☆'
WHEN AVG(Object_Health_Score) < '.2' THEN ' ★☆☆☆☆'
END)
They end up looking this :
Use the Heatmap table:
Re: Reddit Ads Connector -
@omnicarson, Reddit Ads is tricky. In my experience we had to reach out to our reddit ads rep so they could then could create your API key. After this, we were set! Definitely reach out to your rep and let them know you are trying to access your data. :)
Re: Beast Mode as Chart Label
If this is being used on a dashboard, you could have a separate text value card beneath or above the gauge that uses the same data source and changes the text based on the criteria you define in the beastmode. But to have this on the gauge itself is not possible.
Re: Data format from Jupyter to Dataset
I realize this is late to the game, but this is a good discussion that hasn't ever been answered.
What I noticed in @thinh_dao's OP is the datatype for the examples that failed to convert. They're not all lower-case. Int64 and Float64 are used instead of the lower-case equivalents. The best practice for data types is to use all lower-case.
I tested this for float64 and sure enough, if I type my data as float64 in a python dataframe, it will output get as float data type when using the domojupyter.write_dataframe() method. However, the use of Float64 returns a string data type. I repeated this test for int64 vs. Int64. In this case both worked. We are 13 months from the OP to now. I suspect in that time code change made both variations of int64 compatible. float64 remains compatible only to the lower-case variation.
I would recommend sticking with lower-case data types regardless of how Python handles them.
Regarding Date Types
@DavidChurchman is spot on about the date type not working. However, this is a Python/Pandas/NumPy limitation. If you have a column with with a timestamp or a date, and you apply a method to convert to date only, it will convert an either a datetype type or an object type depending on the method used. There is no date only type for dataframes either using Pandas or NumPy.
See for details. Since Pandas gets its data types from NumPy data types this should apply to both.
I tested this when I first learned of it. My personal incredulity got the best of me:
The above example is using domojupyter to load a federated dataset. The column, REQUISITIONING_DATE, comes in as a timestamp. We attempt date conversion using three methods: the pandas method, pd.to_datetime(), the date method from the datetime library, and the somewhat newer datetime.datetime.fromisoformat()
The first return output is the original column that comes in as dateime64.
The second output used pd.to_datetime(). It specified the format of the column data time detail. It returns a datetime type. The time will default to midnight in the data.
The dt.date method is added to the end of the column in the next example. It attempts to force a date only. It will function in Python as a date, but the dataframe date type is an object which is the equivalent of a string in Domo.
Our last example uses the datetime.date.fromisoformat. Older versions of Python use datetime.datetime.strptime() to the same effect. The effect is the same as above. The dataframe data type is a string.
While you can use dates columns within Pandas and NumPy, they do not have an actual date type.
Add'l reference:
RobB
Re: Booking date and shipment date formula
If your dataset already has the booking date, shipment date, shipping dollars and order number, you shouldn't need any additional fields. Have you tried using any of the above suggestions in Analyzer? Start with a table card and see if it returns the results you expect.