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.

Comments

  • GrantSmith
    GrantSmith Indiana 🔴

    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?

  • 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
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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.