Fill down values in a column in DOMO ETL

user020184
user020184 Member
edited March 2023 in Magic 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.

Comments

  • 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?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Its all text and just a general fill down with same values.

     

    I can provide an example dataset.

     

    Thanks

  • Original dataset looks something like this:

    Level 1Level 2Level3Amounts
    Text1   
    Text1abc  
    Text1 t13653
    Text1 t264
    Text1 t335
    Text1xyz  
    Text1 t16434
    Text1 t26346
    Text1 t37557
    Text2   
    Text2abc  
    Text2 t1453
    Text2 t275
    Text2 t3352
    Text2xyz  
    Text2 t174
    Text2 t2523
    Text2 t37

    The final format should look like :

    Level 1Level 2Level3Amounts
    Text1   
    Text1abc  
    Text1abct13653
    Text1abct264
    Text1abct335
    Text1xyz  
    Text1xyzt16434
    Text1xyzt26346
    Text1xyzt37557
    Text2xyz  
    Text2abc  
    Text2abct1453
    Text2abct275
    Text2abct3352
    Text2xyz  
    Text2xyzt174
    Text2xyzt2523
    Text2xyzt37
  • The Original Data Set looks like :

    Level 1Level 2Level3Amounts
    Text1   
     abc  
      t13653
      t264
      t335
     xyz  
      t16434
      t26346
      t37557
    Text2   
     abc  
      t1453
      t275
      t3352
     xyz  
      t174
      t2523
      t37
  • 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"
  • 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
    user01506 Member
    edited February 2023

    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.


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