Fill down values in a column in DOMO ETL
I am trying to fill down values in a dataset that I have via the ETL process and was wondering if there is an option to do so in DOMO.
How are you wanting to fill down the values? Are you wanting to just use the same value? Increase the value? Are you working with a text, date or number column?**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Its all text and just a general fill down with same values.
I can provide an example dataset.
Original dataset looks something like this:
Level 1 Level 2 Level3 Amounts Text1 Text1 abc Text1 t1 3653 Text1 t2 64 Text1 t3 35 Text1 xyz Text1 t1 6434 Text1 t2 6346 Text1 t3 7557 Text2 Text2 abc Text2 t1 453 Text2 t2 75 Text2 t3 352 Text2 xyz Text2 t1 74 Text2 t2 523 Text2 t3 7
The final format should look like :
Level 1 Level 2 Level3 Amounts Text1 Text1 abc Text1 abc t1 3653 Text1 abc t2 64 Text1 abc t3 35 Text1 xyz Text1 xyz t1 6434 Text1 xyz t2 6346 Text1 xyz t3 7557 Text2 xyz Text2 abc Text2 abc t1 453 Text2 abc t2 75 Text2 abc t3 352 Text2 xyz Text2 xyz t1 74 Text2 xyz t2 523 Text2 xyz t3 70
The Original Data Set looks like :
Level 1 Level 2 Level3 Amounts Text1 abc t1 3653 t2 64 t3 35 xyz t1 6434 t2 6346 t3 7557 Text2 abc t1 453 t2 75 t3 352 xyz t1 74 t2 523 t3 70
to fill down data. for each column you want to track each time the row changes.
so you need
Prev_Value (use a LAG 1)
then is_Change (case when Prev_Value != value then 1 then 0
then cum_sum of isChange
in your previous example you should have 1 all the way from Text 1 to Text 2, then from Text 2 onward should be a 2 up until Text 3...
at this point you can JOIN (SELECT * FROM ... WHERE is_change = 1) to your BaseTable
On cum_sum = cum_sum. this will spread VALUE across each row in BaseTable for each cum_sum group.
Then rise wash and repeat for each column. This solution will be easiest in Magic 2.0 or Redshift but probably not in MySQL.
If you're doing it in MySQL you actually can do this with fewer steps user-defined variables and a case that matches on if the value changed or not.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
I'm trying to make this work for me, but I'm not understanding how it's explained...I have order numbers as a parent row with date, customer, and item as a child row. I need to copy the order number down so that it fills the child rows.
Note that the Customer and Date of Transaction could be the same for more than one order. I think I need a more thorough explanation of the steps. Thanks in advance for any assistance you can provide😀0
recently I've faced simmilar issue, and managed to find the way with Pyton.
Code of the Python snippet:
# Import the domomagic package into the script
from domomagic import *
import pandas as pd
# read data from inputs into a data frame
input1 = read_dataframe('YourDataSetName')
# write your script here
input1['column_1'] = input1['ColumnName'].map(lambda x: None if not x else x).fillna(method='ffill')
# write a data frame so it's available to the next action
There is also extra change of DOMO's 'empy string' to Python's N/A's.0
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 80 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive