rco Contributor

Instead of a large case statement that explicitly assigns a week number to every day in the year, you should be able to express your week logic with some combination of YEARWEEK and INTERVAL arithmetic. I don't have enough information to know if this matches your logic well enough to work for future years, but this…

When the join 'on' expression feature becomes available to you there will be a better way to do this, but for now, here is the best I can come up with: The general strategy is to make a table that contains all the unique values of ORDQTY repeated a number of times equal to that value. Then we will join that table to our…

@MattLynn Be sure to check out the edit to my answer; I was missing a pretty significant optimization/simplification. Let me know if it works out.

Regrettably, window functions aren't supported in Magic ETL formulas, and as you've already determined it's not possible to express that window frame using the Rank & Window tile. Including the frame boundaries in the original data, using Beast Mode, or using the Python Script tile are all options. If the whole input…

@SwapnaE It looks like your upsert key was empty in one of the rows your DataFlow produced. If you replace empty strings in it with some other value, does the issue go away?

I would recommend trying to understand the problem a little better before fixing it in that particular way. The error message you're getting back should include an example of a failing value. Something like "Failed to parse text '$1.23' as type…", or perhaps "Failed to parse text '#VALUE!'…". If we figure out exactly…

There's a button here in the formula editor to expand it: Clicking it shows you this editor, which has a searchable function list and documentation: There's also this support article. Though as of this writing that article does not stay up-to-date automatically with documentation changes or additions, so I'd recommend the…

I'll just add some context for anybody still watching this issue. This comment is specific to Magic ETL; Beast Mode (Domo Adrenaline) works differently: Issues like this happen when you mix floats and decimals, or sometimes integers and decimals (since dividing an integer by an integer produces a float). When a decimal is…

Ideally you'd figure out how to express your week system in terms of one of Magic ETL's built in week modes. Then you can use the WEEK and YEARWEEK functions plus your adjustment to arrive at the year and week you want for any given date. The WEEK and YEARWEEK functions both accept an optional second argument to indicate…

Since there isn't a "string contains" operator in the dashboard filters, I've had to come up with a somewhat more tedious way of accomplishing this. It does involve opening and modifying all the cards individually, but only during the setup; once it's done you can use a single control on the dashboard to do your filtering.…

Use the "Add Formula" button in Group By to write something like this: GROUP_CONCAT(DISTINCT FieldC SEPARATOR ', ') Your "columns which identify the grouping" would just be "Field A". Charlie, yes, we certainly can and should add an option to that drop-down to "Combine unique strings separated by ,". Generally, features…

@Charlie Bantoft It sounds like you want this formula: GROUP_CONCAT(DISTINCT Model SEPARATOR ', ') Use the "Add Formula" button instead of the "Add Column" button in Group By and use that formula in place of the "Combine strings separated by ," aggregations. Don't forget to replace Model with Colour for the other one.…

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…