rco Domo Employee

For the time being, the only way to handle this is to compute a new column upstream using the two you want to use as your partition key. Using an Add Formula tile upstream of your output:

This isn't currently possible. Magic ETL does not usually generate SQL to run a DataFlow. In fact, when you use the SQL action in Magic ETL, it does the opposite thing: It translates the SQL action's query into a graph of simpler actions. In other words, the fundamental "language" of Magic ETL is the action graph, not SQL.…

What you're missing is a column that you can properly partition on. Your partitions right now are implied by row order; there's no single column that can be used to associate rows from a given segment with each other unless you also consider row order. We can fix that with two tiles, an Add Formula followed by a Rank &…

The bug has now been fixed.

Thank you for the additional detail. There is a bug here after all! While nulls are being ignored in most cases, if the first null value in a group is null, it is generating a separator. I'll make sure this gets fixed as soon as possible. In the meantime, we can at least trust that these unexpected separators will only…

Magic ETL's GROUP_CONCAT does ignore nulls, as is the case in MySQL. You can test this using the SQL tile: Result: Is it possible that there are values in id that are blank but not equal to the empty string? What happens if you do NULLIF(SQUASH_WHITESPACE(id), '') rather than just NULLIF(id, '') ?

It would definitely be an improvement to be able to reorder aggregate columns in Group By. Column order as a whole is not given the attention it deserves in Magic ETL. This workaround may be unwelcome; in most cases this will be more of a pain than just redoing the aggregates from the point where you want to reorder them.…

Could you screenshot the configuration of both value mappers and provide the data types of the relevant columns up to the point where the preview succeeds? You can see the data types in the Preview table underneath the column names.

Magic ETL's SQL tile does not support all SQL functions and statements*. A list of supported functions can be found here: https://domo-support.domo.com/s/article/360044289573?language=en_US The PERCENTILE_CONT function is not a supported function. The WITHIN GROUP clause is not supported for any functions in Magic ETL, nor…

I tried both expressions with both the strings in your example and it seemed to work in all cases. Are you certain that the exact string on which it isn't working is this one? Company Name - 2024-07-01-2024-07-31 - GPPC - YTUBE - VYT - 63784 - 2782885 - 458573695

If you have the Join → Rank & Window → Filter, and: 1. You're using left outer join with Raw Data on the left 2. You have your Rank & Window partition by a unique ID column of Raw Data 3. You're using the Row Number function in Rank & Window 4. Your Filter condition is rank is equal to 1 (where rank is the column…

It may be easier if I explain it like this: First off, I'm assuming that your Campaign table has just one row for each Campaign, where the Active Date is the date it begins. From your example, it seems like you might have two rows per Campaign, though, and I'm not sure how to interpret those. The first thing we need to do…

I assume you mean you want everything before 1/31/2024 to be .5 and everything after 2/1/2025 to be .6. In other words, you want the earliest campaign to apply on into the past forever, and for the latest campaign to apply on forever into the future, or at least until another campaign is added with a later date? The…

This would be three tiles, a Join, a Rank & Window, and a Filter. Start by joining like you have, where every date joins to every campaign with an active date less than or equal to it. Then you just need to reduce that down to just the row with the greatest active date. In the Rank & Window, partition by a row identifier…

The Group By emits just one row, the sum for the whole table. The cross join just joins that single sum value to the original table; the final row count matches the original row count.

There is no "FIXED()" in Magic ETL, but you can replicate its behavior with a combination of Group By and Join. In this case, it you'll want to configure a Group By like this: And the subsequent Join like this: And finally an Add Formula like this: Arranged like this:

The flow consists of the following: 1. Add Formula. New column name: "Row Number", Formula: ROW_NUMBER() 2. Rank & Window. Order by "Row Number". No partition columns. New column name: "ID Count", contains Count of "ID" from Unbounded Preceding to 0 Following. 3. Filter. Condition: ID Is Not Null. 4. Join #2 to #3. Type:…

You could preserve your historical data by making a new DataFlow that uses the problematic output as an input, and then copy its contents to a new DataSet. Then you could bring that copied DataSet in as a secondary input to your original DataFlow and merge the new data with the historical data in any way you choose (Append…

Upsert works exactly like Append except that any pre-existing rows with the same key value (the value in the specified key column(s)) as a newer row will be removed. It isn't currently possible (in Magic ETL) to remove rows with a given key value entirely, nor is it possible to update the key value of a row. Reverting to…

In addition to the correction mentioned by @ggenovese, this segment also needs to change: date(Job Open Date)<'10/01/2024' . An unfortunate quirk of Magic ETL is that comparison operations with strings always happen as strings, which is usually not what you want. You must convert the right side of the comparison to a date…

Try an Add Formula tile and the TIME_TO_SEC function: name: duration_sec expression: TIME_TO_SEC(`duration`) This should work as long as all your string values are in that hours:minutes format.

@vaco Timestamps and Dates columns in Magic ETL don't have a format. They will always be displayed in a way that is determined by the table or card you're looking at them in, along with your company settings like timezone and locale. If you want to force a specific format everywhere, a string column is what you need. As…

What about this?: REPLACE(process_timestamp, 'T', ' ')

The flow looks like this: Inverse Amounts configuration: Join configuration: Restate Include / Exclude configuration:

Okay, so these "probably 5 rows" from the same campaign each have an impression count, but they're not divvied up the way you want. You want the campaign divided into exactly 3 rows, and you want the impressions divvied up 40%, 40%, and 20% between those three. The first thing you may need to do is a Group By the campaign…

Sure, you could branch it three ways (perhaps into three different Add Constants tiles) and then union it with itself using Append Rows. But I still don't feel like I understand what you're trying to do. You've said, "Lets say I have a million impressions in Campaign 1. I am looking to say take 40% of those and call it…

Are you trying to randomly assign rows the value A, B, or C with approximate probability .4, .4, and .2 respectively? Or do you need to divide the table as close as possible to perfectly in that proportion? If approximate is okay, you can do an Add Formula where you'll first generate a random number and then set the value…

I'd branch the Input DataSet tile into two Filter tiles, one with the rule amount > 0 and the other with the rule amount < 0 . Then, on one of the sides, use an Add Formula to create a column "match_amount" with the expression -amount . Then join the two branches on client ID, transaction type ID, transaction date, and…

For a date column called "d", the filter formula would be: dayofweek(d) = 7 and dayofmonth(d) < 8 and (year(today()) * 12 + month(today())) - (year(d) * 12 + month(d)) < (case when dayofweek(today()) = 7 or dayofweek(today()) < dayofmonth(today()) then 7 else 6 end) This checks three conditions: 1. The day is Saturday 2.…

Unless it's a Cloud Amplifier DataSet, which I don't believe it is, then the connector is exporting the data from your DynamoDB. Most connectors export to CSV files. Those files are the form in which Magic ETL sees your data.