-
Limit on ranking tiles
I added 20 ranking tiles to me Magic ETL, but it fails stating out of memory. I removed 4 of them, and now it runs fine. Is there a limit to the number of ranking tiles you can use? The input is only 2 million records.
-
How to create 2 Output Dataset in DOMO (unable to load this dataset issue)
Hello everyone - Good Day! I just need some inputs. I have a dataflow with 4 input dataset and 1 output dataset. When I add another output dataset and hit preview, it showed all green checks. However when I saved and ran it, that 2nd output dataset shows as "unable to load this dataset"? any reason why it is unable to load?
-
Quick Select Multiple Columns in "Select Column" ?
I am wondering if there is a way to choose multiple columns quickly when using "Select Columns" in an ETL. I do not mean using Add All Columns. We do not know SQL, and we have massive DataSets with a large amount of columns (50+), but only need a mix of about half & half between 2 Datasets. Having to scroll through &…
-
Beast Mode to compare the record from the recent Monday to the last Monday
Hi All, I have a dataset that has the the different schedule codes by date at a PO number level..I am trying to identify the records from the most recent monday and compare it to the immediate last monday and then flag the combination of (POnumber /schedule_code) to be old (meaning this combination existed the last monday…
-
MagicETL/SQL to join datasets using a comparison between values
I am trying to get a geolocation based on the IPNumber. Dataset a: ipaddress ipnumber (translated from ipaddress) Dataset b: lowIPNumber highIPNumber Country Region City I would like to add the Country, Region, and City values to an output dataset: ipaddress ipnumber Country Region City where a.ipnumber >= b.lowIPNumber…
-
Calculated Fields - Pipedrive ID's
Hi, I am pulling in data from Pipedrive and need to use Calculated Fields to convert Pipedrive Field ID's into text. Is there a way that I can do this once instead of doing calculated fields for each metric?
-
Period over Period in a table
I'm attempting to do a Period over Period in a table, but want to keep the date range to previous week/month/quarter to keep it auto-updating and grouping the data in a particular way. My idea was to create a new column in ETL that is the same as the column I'm trying to do a Period over Period calculation, but from the…
-
REGEXP_REPLACE in ETL add new line?
I have data in Domo for an address that includes a new line character. This data was brought in from NetSuite in this format. I am trying to add additional data that matches this format. I have the following data: Ship Address: attn~company~addr1~addr2~city, state zip I would like it saved as such: attn company addr1 addr2…
-
How to remove empty columns from a dataset?
I am using a DOMO connector to import Salesforce data. The imported data has 30+ columns with no value. Can a query be written to remove all the columns with no values? I know I can write a script using Python/R but we don't have a scripting tile in our package.
-
How to find a max value from multiple aggregates
Hey guys, I'm looking at our collections information from the last two years to find our best collections month during that time frame for each of our locations. Before when I had to do this I just did it in ETL using a group by function, but this case is a little more complicated as I'm wanting a specific set of…
-
Pendo connection: how to add a date column to a dataset?
Hello! I have created a Pendo connection to domo but a date column was not pulled over with the data. I was wanting to add a column to the dataset and put the date that the corresponds with the data but am struggling to do so. Does anyone have any ideas on how to do this?
-
Duplicate data points for Weekly Reporting. I need the most recent day for weekly reporting.
Our subscription data is collected on multiple days throughout the week and creates a snapshot of that point in time. I want to see what that week's total was based on the most recent day. I don't mind removing the row with an earlier start date.
-
How do I create a rate with 1 row of data then filter it by unique cells of another row?
I'm pretty new to Domo and work in healthcare but essentially I have an excel style sheet of data. In 1 of the rows is the list of different admitting providers and in another row it specifies if the patient was inpatient or observation. There is a column/unique account number for every patient in this data sheet. I am…
-
Comparing two datasets for missing sites
Hi, I'm looking to compare two datasets from Smartsheet to identify which sites are missing from one dataset. For example, show me all sites that are in 'Master Schedule' but aren't on 'Secondary Schedule', both sheets have identifying numbers in column 'URN'. What would be the best way for me to do this? Would I join…
-
Replace text and value mapper failing
I have a couple data flows that use replace text or value mappers to remove certain text characters from a number column to then convert to an integer. Examples of text we are finding in our source data are "#DIV/0" and "--". However, when those characters don't exist at all in the source data, the replace text tile fails…
-
Replicating SQL statement in Magic ETL Formula tool
Hi All, I am trying to replicate the following SQL statement in the MagicETL formula tool.. (SELECT MAX("FISCAL_YEAR") FROM table WHERE "DATE_VALUE" = CURRENT_DATE) AS CUR_FISCAL_YEAR In the Magic ETL tool I am giving the name of the output column as CUR_FISCAL_YEAR and Write Formula as MAX(`FISCAL_YEAR`) WHERE…
-
Magic ETL formula Help
Hi All, I am trying to use the following transformation for joining but I am getting an error...Can you please help! CAST(`DEALER_NO` as varchar(8)).. I am getting the following error... Syntax error in expression at character 20 on line 1 : cast (dealer_no) as varchar()) Thanks, Arun
-
How to always correct the date to the 1st.
Hello all, I want to know the code for ETL "Add Formmura" function. The column ”Data” will always contain a value for the year, month, and date. When the value comes in , I want to set the date value always to 1st by using " Add Formmura" function in ETL as like below picture. Could you let me know the code to enter into…
-
Group By -- all additional columns
I have a data set that looks something like this: Rep Name --- Department --- Month 1 --- Month 2 --- Month 3 etc. I want to do a 'group by' as some reps appear on multiple rows and I want to sum the Month total for those reps. That part is easy. Is there a way to automate that all future months added also get that…
-
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…