-
The correct regex to remove the first character?
I have a column( that illustrates a dollar amount but is in string format. What I am trying to do is to remove the dollar sign ($) that is the very first character in each row, so i am only left with the numbers. I have tried the left/right substring but each dollar amount is different length so I have ran in to issues.
-
Write a Filter Formula using Magic ETL Filter Tile
Hey guys, so I am trying to utilize the filter formula feature in magic etl, but I can't seem to get it to work. When I run the following formula, no data appears. Using Excel, I performed a concatenate on all 517 websites that I would like to be filtered out from my data in order to build my formula. I've also used AND as…
-
How populate a new column with data based on data in other rows
I have a dataset consisting of Google Analytics data. One of the columns identifies their Userid (for our ecommerce portal) if they authenticated. Another column identifies their GA session ID (during the visit to our site before and after they authenticate). I am interested in capturing all the activity for a user before…
-
Is this doable? Seeing percent of total day sales by hour using Timestamp column.
Hello! I have a problem that needs to be visualized and it was phrased to me like this: I need to see what percentage of sales each hour had for the total day last year then I need that percentage multiplied by the daily goal to get an hourly goal for today. So we have this chart right now: this show us net sales today,…
-
How to SUM working days correctly?
Hi everyone, I have a strange problem. Probably more of a SQL question than a Domo question, but here we go. I have a typical date column (yyyy-mm-dd) and a column called "total working days in month" where it returns a aggregate value of the # of working days for each month. Below is an example (just assume every date is…
-
How to append data to the output dataset?
I have a workbench job that will run daily. It will get the data with current date. I then needs to filter this data and create two data sets using the DataFlow ETL tool. How do I append data to the DataFlow ETL output table? DataFlow ETL creates a new table every time it runs. I realize that One way to achieve this is to…
-
Anything like Difference matrix, Difference between two pivots, difference within the value field?
I've searched around but haven't had any luck finding what I need, but I am not sure if I've searched the best way either so please refer me if this is a duplicate. I'll use dummy data to ask my question, but effectively I need the difference between two values across a couple of dimensions. The goal is different from our…
-
Handling multiple aggregation levels from source data
My company uses syndicated POS data from sources like IRI, NPD, and retailer POS. There are many measures that cannot be aggregated from the item level data, so I need to bring in pre-aggregated data in addition to the non-aggregated data. I was planning to have one column that indicated the aggregation level, but when I…
-
Reg. Expression to replace a list of names with a single character.
I have a column that is in a string format, and of course contains names. Majority of the rows are an empty string. What I am looking for is to take the rows that do contain a name and replace them with a 1. i have been trying to use replace text tile then trying to use a regular expression in the 2nd step, then replace…
-
Changing a value to an integer in the ETL
I have a column that uses an 'x' as a flag, any row that isn't flagged with an 'x' is left as an empty string. What I need is to replace the 'x' with a '1' to be used in a later count. this should be straightforward but I keep getting my wires crossed with the order of operations.
-
Previous day values - Following Day Values Restart Calculation Each Month
Hey guys, I have a dataset that appends each day to include the daily downloads, and resets the calculation on the 1st of every month. This issue is, it's cumulative totals, so if yesterday we had 40 downloads and the following day we had 15 downloads, the total for the following day would come in as 55 total downloads.…
-
DOMO Regex Extraction ETL
I'm looking to extract a work order number which contains the letter W followed by 15 digits (Ex. W571191476628566). The order number lies within a string containing notes content that has been exported into domo. (Ex. "Customer called in to discuss work order W571191476628566, and asked about order status"). In the domo…
-
calculate difference between two dates
Hi, We have one filed name "Inquiry Date". and we wanted to calculate the difference between the two inquiries. we need output like mentioned below screenshot (Difference between two dates) eg: (01/01/21) - (01/01/21) = 0 (02/01/21) - (01/01/21) = 1 (07/01/21) - (02/01/21) = 5 (15/01/21) - (07/01/21) = 8 Please suggest a…
-
Dataflow creation best practice and recommendation
A lot of the dataflow creation I do is taking 2 datasets that differ slightly and don't have an easy 1:1 comparison that makes joining the data very easy. usually I am using 1 report for almost the entire visualization, and puling a column or two form the 2nd report. Since the reports aren't in an identical format, and…
-
Join Function in ETL
Hi, I am trying to join a table on my DataSet, using the left join function. My original DataSet contains only 118k rows, and the other table contains 1405 rows. I thought that the output DataSet will stay 118k rows, and just have addition columns on it. However, the output DataSet is 4.6M which is way more then I…
-
How to split values from one column in to 2 columns?
I have a column that contains values that are 4 digits. I want to divide them up in to 2 separate columns. The first 2 number are the % of the discount, and the last 2 are the length Ideally it would be the % and contain 30, 40, 35, 40 ,etc... Then Length would have the last for digits like 02, 02, 03, 02, 04, etc.. I am…
-
How to delete portion of text in a column in the ETL?
I have a date column that is in this format: The format of the date is fine, but what I am trying to do is get rid of everything including the 'T' and to the right, so that all that is left would be 2021-06-27. If a solution within the ETL is possible that would be great.
-
Using Select Columns to reorder columns in Magic ETL
In Magic ETL, a Select Columns tile was inserted just prior to the Output Dataset tile in order to reorder columns. The columns in the resulting Output Dataset were not in the order specified in the Select Columns tile. Is there something else that needs to be considered so that the column order is retained in the Output…
-
Data Flow help
Hello, Experts, I am humbly reaching out for some help. I have a vendor sending a snapshot of a database table every day and not sending me diffs. I am trying to determine the diffs. The connector type is SFTP CSV. The file they send daily is overwritten daily. I currently have the data connector appending the data from…
-
Improve Dataflow Efficiency
Hello, Domosapiens, I currently have a data flow that is taking 14 min or greater to run. The design is as such: MySQL connector that first runs once to pull in all historical data (7.8M rows). Ex full historical pull query: select * from table; change dataset settings to merge method by changing the query to select * from…
-
Adding an "Activity_Date" to a nightly refresh dataset.
I just set up a connection for a dataset that doesn't have any fixed 'date' column. The only date column it will have will be the batch_last_run date. On a nightly basis this dataset will append and start to build history. How can I add a new date column in a format such as 'Jul 1, 2021', so that the new data and old data…
-
ETL
I have a column with multiple categories values, I want to convert this categories values to column labels. How do I do this. In the screenshot attached below I want to convert values of column '123 1' to column labels.
-
How do i trim out an email address from a field with a string?
How do i trim out an email address from a field with a string? Example of Field: Yes, we accept this current pricing and would like to proceed with this order. Thank you, Bob Dole Lab Research Analyst Poss Lab—Dole University bob.dole@some.edu Thanks
-
Dividing in SQL
I currently have this code: categorizing some leads. Select `Year-Month` ,`Lead Source` ,`Product Line` ,`Product Models` ,`Country` ,`Early Stage No Movement` ,`Stage 0 to Early` ,`Early to Lost` ,`Early to Won` ,`Early to Late` ,`Late to Lost` ,`Late to Won` ,Sum(`Early to Lost`+`Early to Won`+`Early to Late`) AS 'Total…
-
Pivot Help
Hi, I have a situation where a column my have different rows of the same values, but the values are in different orders and therefore they are different buckets in cards, when they're actually the "same" thing. And example of this is in A1:B3 of my attached image, where both rows under the BH_TAT.Antigens column have the…
-
Regards to transforming data prior to joining with another data set.
Attached are 2 different samples of data. Both are in similar format, and have plenty of same columns. The price list is where the bulk of what I am going to use will come from. It is the full inventory for a site. the total number of units, number of occupied, number of vacant etc... It is rolled up in to a single row for…
-
How can I create a new column with extracted numbers in SQL or ETL
I have a column called 'description' which can contain multiple elements. One of them is 'Order #1234567890' (or other digits). I want to create a new column that just extracts the numbers in SQL or Magic ETL (so that I can do Beast Modes etc. on it afterwards). How can I create this column? Numbers may exist in other…