-
Generate Date Range Column from Two Date Columns
Hi all ! Im trying to generate a date column with a range of dates from two columns: Table I have (date format (mm/dd/yyyy) ) Id start_date end_date field1 1 01/01/2019 07/01/2019 text1 1 07/01/2019 12/01/2019 text2 2 01/01/2019 02/01/2019 text3 The idea is to generate a list of dates between start and end date with an…
-
Domo Python error
i am using the python tile in domo magic etl i am trying to cast a column to a category but when i try and read the dataframe to the write_dataframe i get an error # Import the domomagic package into the script from domomagic import * # read data from inputs into a data frame raw = read_dataframe('Source 2') #print('Before…
-
Python Tile Typecasting while Reading Data
A quick question in the magic ETL python Tile API method read_dataframe can you include the dtype parameter and use a datatype dictionary eg import pandas as pd import gc dtype_dic = {'%Account_Plan_Key':'category'} # Import the domomagic package into the script from domomagic import * pd.options.mode.chained_assignment =…
-
Deduplicating a table based on the content of certain columns
I have a dataset that has about 20 columns in it. The first column contains ID numbers and a lot of the ID numbers are duplicated multiple times. All the other data in the columns are also duplicated multiple times except one column named last_updated. The last_updated column lists a date. I would like to de-dupe this…
-
Formula cannot be added
Hello, I am a a beginner user on Domo (used before on Alteryx). I have tired to add formula the the canvas on Magic ETL , but it looks very funny and does not allow me to write a formula. It seems very silly thing, can you advice what I am missing here ? Please see the screenshot attached. Many thanks, Matan
-
Creating a join from a lookup table
I have created a dataflow that joins two sets of data together, however when i join them together it's dropping data because it doesn't have anything to match with. How would i create a dataflow that would match all the data and any of the unmatched records would also stay?
-
Help with Magic ETL to find the status of SKU items manufactured
I have a delay table (as below) where a SKU is identified by the combination of mmodel and srno and the snapdate.. A SKU present on a specific snapdate indicates the SKU was delayed on that date Delay Table mmodel Srno Snapdate MX201 C12341 3/21/2021 NM213 L1234 3/21/2021 JK1231 K1561 3/21/2021 KL1234 MJ1234 3/21/2021…
-
Historic Timesheet Analysis
I'm working on an analysis of our historic timesheet data and trying to calculate our employee utilization (actual hours per day/8), so that we have a historic benchmark and can compare to projections for future utilization. The challenge I'm running into is actually cleaning the data. It's generally well-structured - in…
-
Help with Magic ETL to calculate the distinct count of pickup numbers by plant
Hi All, I am trying to get the distinct count of pickup numbers by plant and pickupmonth year..Please find my input table below...Can you please help me with the Magic ETL for the same...I am also attaching the excel file data.. Input pickdate picknumber Plant 1/3/2022 L009803 Seattle 1/3/2022 L009803 Seattle 1/3/2022…
-
Forecast monthly expense
I'm trying to create a formula within ETL that will help budget owners understand how their expenses will be accrued over time. Ex: There's one Purchase Order that has $10,000 left to invoice, and the PO end date is July 31,2022. Our accounting team would expense them evenly for $2,500 for April - July. So I'd need to…
-
Moving one column under another
I have a table with 5 columns: column 1 is an ID, columns 2 and 4 are codes, and 3 and 5 are corresponding names. I need all the codes in one column, and all the names in one column. These means each ID will now have 2 rows, one for each code/name. How would I go about moving data from one column to another in Magic ETL?
-
Magic ETL - Group By Tile - Sum Multiple Integer Columns
Hi all, I have a Magic ETL flow going currently where I have three input datasets that I am appending into one dataset. All three datasets have the similar fields and columns, but one of the datasets I need to manually SUM three individual columns across each row to get the "Total" count that exists in the other two…
-
Drill Path Filter to show Specific Apt's without a Specific Procedure
Hey guys, I'm worried that the answer to my question is that it has to be done in ETL, but I'm hoping it can be done as a beast mode. So the data in question looks similar to what I have below: Doctor | Procedure | Date | Apt # | Patient Dr. B | D0330 | 4/1/22 | 80136 | I. M. Madeup Dr. B | D0126 | 4/1/22 | 80136 | I. M.…
-
Calculating the count of entries not present in the future date in comparison to the previous date
Hi All, Hope you are doing well!...I am trying to build a magic etl for getting the number of entries (combination of sno and mmodel) that are not present in the current date of reference in comparison to the immediate previous date of reference...This will help understand the count of the number of entries that have gone…
-
ETL or beast mode for Dynamic assignment of month based on current date
Hi All, Hope you are doing well..I am trying to convert a plan table as below into the table below based on the current date and the reference table Reference table: So I need to basically map the column names (FC1,FC2,FC3...input table) to fiscal month based on the current date and looking up the reference table for the…
-
Looking up values from secondary data source but for multiple columns in primary data source
So I'm having some trouble wrapping my head around a solution for this scenario in Magic ETL: -I have a primary data source with multiple columns where values are stored in shorthand like "hc_res" but I want to display reports with pretty names like "Healthcare Resources". The mapping of "hc_res" to "Healthcare Resource"…
-
How can i write this sql statement in domo?
where a.Customer NOT IN(select distinct Customer from CustomerSalesData where InvoiceDate < '2020-07-01') Statement below is a column I use for an sql query.. ROW_NUMBER() over(partition by a.Customer order by MIN(a.InvoiceDate)) as OrderNum I am getting an error while trying to convert this statement. I am not sure how to…
-
Checking the existence of an entry in the immediate future date and counting in future date
Hi All, Hope you are doing well!...I am trying to build a magic etl for getting the number of entries (combination of sno and mmodel) that are not present in the current date of reference in comparison to the immediate previous date of reference...This will help understand the number of entries that have gone off the list…
-
column referenced isn't found but exists in the connector
In ETL formula tab I am trying to rewrite a column name and am getting an error that says the column referenced isn't found. The column name pops up in the list of available columns to re write but then errors when I try to validate the formula. Is there any fix to find this column?
-
Comparison of data to the immediate previous date for every consecutive date
Hi All, Hope you are doing well!..I am trying to build a Magic ETL workflow to calculate the number of entries (combination of sno and mmodel) occuring new in the current date of reference in comparison to the immediate previous day.. Please find the sample data below: Input table sno mmodel snapshot_date…
-
Filter a DataSet with values from a Google Sheet?
Hello, I have a dataset that I would like to be able to dynamically filter based on values in a Google Sheet. This feels like a fairly simple MagicETL to me but I'm stumped at the moment. In short, the PROD dataset is good to go and I would ideally be filtering one column from the PROD set based on values in one column in…
-
Adding Space to Postal Code TEXT String in Magic ETL
Hi, I might be missing something but looking for some help to add a space to a postal code text string using Magic ETL. I am trying to change a postal code from S4L1A5 to S4L 1A5. thx!
-
Adding business days to a date via MagicETL
Hi, I'm currently calculating a deadline using a dynamic formula in MagicETL. It looks like this: CASE WHEN `issue` = 'bug' THEN ( CASE WHEN `priority` = 1 THEN ADDDATE(`CreationDate`, INTERVAL 4 DAY) END ) The calculation works great, but I was wondering what would be the best way to update it to 4 business days, rather…
-
Creating buckets for unique ids to fall into, based on the sum of donations of those unique ids
Capturing donor frequency: We want to count the number of donations a donor has given per procedure. The amount of donations is the bucket I want donors to fall into. So, if we had 100 donors give 4 donations, we want those donors to fall into the bucket '4', and then we would count the unique donor ids. The part I am…
-
Magic ETL Formula Editor Tile - Minor glitch
I am just wondering if anyone else has been experiencing this. I couldn't find any related posts. In the formula editor tile, I will literally click on the column name from the list below. When I am done with the formula and click "validate" I get an error regarding the column I selected and it says "table doesn't exist"…
-
Can you find a Beast mode by it's function?
Hi We have a primary data feed and that datafeed contained a 15 digit ID. We recently updated the datafeed to the updated 18 character Account ID and in Domo we used to perform a String Operation that performs a left trim from 18 to 15 characters prior to using a Join Data tile. We wanted to find all of the Beast Modes…
-
Creating complex ranking using certain values within a dimension
Hello all! I am attempting to create a sankey diagram that shows a customers activities leading up to a certain conversion. Each customer can have multiple conversions so I want to rank each activity leading up to a conversion, and have that rank reset after the conversion happens. Having some trouble theorizing how to do…
-
Table vs SQL Data Flows and which functions does Domo not recognize? (I'm confused)
Hi, My CSM informed us that window functions are enabled. I would like to write the following via ETL, or a My SQL dataflow (either way is fine), but I don't know whether to pick a Table or SQL transform, and either one I pick will not accept the following statement. SELECT *, sum(a) over (partition by b order by a range…
-
Unique ID within parameter
I'm working on an ETL. I want to create a unique ID for each row of data with the caveat that the ID should be the same as the previous value if a value 0 appears in a flagged column. I was thinking of doing this through a window function of row number and then a function tile, but I can't figure out how to achieve this…
-
How to remove a decimal in an area dimension column?
I have a column that showing the dimensions of a unit. 5x5, 5x10, etc.. the column is in a text format and the data is coming in to Domo in a strange way, it looks like: What I want is just the more standard: 5x5, 5x10, 5x15, etc.. In the ETL how could i get rid of the .0?