Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Cloud Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Cloud.
SOLVED

How do I fill horizontally (across columns but within the same row)? I need the solution to be generic. So setting the values to a hard coded string is not sufficient.

446193eada4dc478fb22
8 - Asteroid
 
21 REPLIES 21
Trifacta_Alumni
Alteryx Alumni (Retired)

Hi, Harry--

 

In the Transform Builder, enter Edit with formula.

 

You should be able to apply the formula across multiple columns. If you need to refer to the current value in the column as part of your formula, use the $col reference in your formula.

 

Does that help?

 

Cheers,

-SteveO

 

 

 

446193eada4dc478fb22
8 - Asteroid
Hi Steve, Thanks for getting back to me. Your answer provides useful information. However, it does not quite answer my question. I will try to be more specific. I am trying to solve the problem of populating all empty cells in row 1 to the right of the ‘2017’ in the grid below with the value from the last cell to the left that has been populated. So the cells in range C1:M1 should be populated with 2017 and 2018 for O1:W1. I want the solution to avoid hard coding if possible. [cid:image001.png@01D4A2BD.BDAD0AE0] Harry Burn
Trifacta_Alumni
Alteryx Alumni (Retired)

Don't have access to software right now, but I do know that there is no horizontal FILL function.

 

You could try to transpose the data and then use the vertical FILL function.

 

Your link is trying to open as an email address for some reason.

 

Cheers,

-SteveO

 

446193eada4dc478fb22
8 - Asteroid
Thanks Steve, Data now attached. Is transposition followed by vertical fill really the only way? It feels a bit awkward doing it that way. Regards Harry Harry Burn
446193eada4dc478fb22
8 - Asteroid

I have tried transposing the data, as suggested. In order to using the FILL function, it is necessary to add a column for a row count/ primary key to use within the Order clause. However New Formula: SOURCEROWNUMBER creates a column with 'No valid values' as the data is no longer in its original format and ROWNUMBER requires input for Order, which does not help.

Trifacta_Alumni
Alteryx Alumni (Retired)

Hi Harry,

 

Please have a look at this community thread, as I think it covers the situation you have encountered: https://community.trifacta.com/s/question/0D51L00005aT1EaSAK/how-can-i-fill-an-empty-value-with-the-one-from-the-previous-or-next-column

 

Please let us know how it goes.

 

Best regards,

 

Nathanael

446193eada4dc478fb22
8 - Asteroid
Hi Nathanael, Thanks for your response. The link you have sent does not answer the question, primarily because I cannot upload the attached Wrangles to DataPrep, where I am working (see Stack Overflow question from Mar 18<>). So it is hard to see how the proposed solution matches my requirements. I have tried using SOURCEROWNUMBER on the original data, then using UNPIVOT , but when I try to use SOURCEROWNUMBER on the transposed data, I still get ‘No valid values’ in the column created. See attached for screen print including wrangle language for recipe, .wrangle file and original data. Regards Harry Harry Burn
Trifacta_Alumni
Alteryx Alumni (Retired)

Hi Harry,

 

The approach I was referring to in the other thread works like this:

 

  1. Derive a new column using SOURCEROWNUMBER() and call it, e.g., row_num
  2. Unpivot
  3. Fill as desired, specifying row_num for the custom sort order rather than SOURCEROWNUMBER()
  4. Pivot to restore the data
446193eada4dc478fb22
8 - Asteroid
Thanks Nathanael When I try to use FILL will ‘row_num’ in ‘Sort rows by:’ (I believe this is the field for Order input), I get the following error: Error Column row_num does not exist in schema. Harry Burn