Fill down values in a column in DOMO ETL

user020184

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.

GrantSmith

Hi @user020184 

 

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?

Unknown

Its all text and just a general fill down with same values.

 

I can provide an example dataset.

 

Thanks

Unknown

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	7

Unknown

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	7

jaeW_at_Onyx

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.

 

megg009

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.

Example Table

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😀

user01506

Hello,

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

write_dataframe(input1)

====

There is also extra change of DOMO's 'empy string' to Python's N/A's.

zmenzer

If anyone else runs across this issue, I'd suggest looking at @MichelleH answer (which offers a step by step walkthrough) here:

https://community-forums.domo.com/main/discussion/56483/first-non-null-value-from-column-in-formula-tile