-
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…
-
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…