rco Domo Employee

First, we should solve problem #2. Then, we'll create a canonical lookup table from Number to City to solve problem #1. Use an Add Formula tile to make two new columns, Canonical Number and Canonical City like so: Canonical Number formula: NULLIF(STR_DIGITS(CASE WHEN City BETWEEN '0' AND '999999' THEN City ELSE Number…

Could you clarify the problem you're having with Rank & Window? @ST_-Superman-_ is correct in saying that the Lag function is what you want. A lag of 1 will give you a new column whose values are all offset by 1 from those of its source column. That's not a complete solution, though, since you'll still have nulls in every…

Thanks for bringing this up Zac. We've updated the in-place Magic ETL documentation for the WEEKOFYEAR() function. The new docs will mention the discrepency with MySQL and recommend that WEEK() always be used over WEEKOFYEAR() in the current version of Magic ETL. We can't immediately change the existing behavior of…

It's a recent addition and not yet mentioned in the docs, but Magic's GROUP_CONCAT does support the DISTINCT keyword now. A Group By tile with grouping column Account and the formula aggregates GROUP_CONCAT(DISTINCT Category SEPARATOR ', ') and MIN(Date) might be what you're looking for.

Raj, are you in the Filter tile's formula editor there? Filter expressions must be boolean (true/false/null). You may want to use the Add Formula tile to create your quater string, and then perhaps multiple filter tiles with expressions like my_year_quarter = '23-Q2' to separate your data into different sets (if that's…

write_dataframe is failing here due to column names, not values. Each item in pivot_df.columns is expected to be a non-null non-empty string. Either pivot_table is returning a DataFrame with null column names, empty string column names, or non-string column names.

If your goal is to get a result that is a time string like your input, I believe you want something like CAST(SEC_TO_TIME(SUM(TIME_TO_SEC(`TALK TIME`))) AS CHAR) If you only want the hours (as a floating-point number), it would be SUM(TIME_TO_SEC(`TALK TIME`)) / 3600 If you want integer hours, invoke the DIV function…

GROUP_CONCAT, which is identical in behavior but different in syntax to LISTAGG, does support the DISTINCT keyword as of relatively recently: GROUP_CONCAT(DISTINCT customer_name SEPARATOR '/')

I'd Group By docnum to produce the 'First non-null value' of Quoteref and then Join the result of that Group By to the original sales data on docnum. If there's any possibility of multiple distinct Quoteref values within a given docnum and you don't want to lose that information, you could do a formula aggregate instead…