Fill down values in a column in DOMO ETL

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?

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.

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.