Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Need to perform several multi-row formulas, but the number will be dynamic based on data

Mtvbqb
7 - Meteor

The number of columns in my data will vary dynamically.

 

If the next row of data is empty, I want to copy it from the above row. The formula for my multi-row formula looks like this:

 

IF IsNull([F11]) THEN [Row-1:F11] ELSE [F11] ENDIF

 

Where F11 is an example column. The issue is that I may need to do this, 4, 6, or 20 times on a file depending on the data included. Is there a way I can systematically check each column and perform the multi-row formula?

7 REPLIES 7
grossal
15 - Aurora
15 - Aurora

You can use the Multi-FIELD-Formula to achieve this!

 

Give me some sample data and I'll build up a workflow for you 😃

AbhilashR
15 - Aurora
15 - Aurora

@Mtvbqb- assuming you are looking to apply the multi-row formula logic across multiple columns, you might have to transpose the data, apply the multi-row formula on sections of data (using the GROUPBY option within the tool), and do a crosstab to bring it back into its original shape.

 

There might be additional nuances to the approach, and is dependent on your data structure. Provide us a sample dataset if you can.

Mtvbqb
7 - Meteor

Data is attached grossal.

Mtvbqb
7 - Meteor

An important inclusion is that if no data exists at all in the column, I want to pull the column name and populate that into all fields. I had planned to do that by simply not pulling in the first row as my header, but if you can think of a more elegant solution, I am all ears.

grossal
15 - Aurora
15 - Aurora

I think I got what you want, but it looks a bit odd, so does the sample data.

 

grossal_0-1585511744362.png

 

What happens:

1) I Filter out the Total Record rows in the end

2) Shift columns to rows (Transpose)

3) Sort them

4) Multi-Row Formula to handle your replacements 

5) Cross Tab to bring them back into shape

6) Sort again to get back to the original order

7) Auto Field to adjust the Field Types

 

I hope I got what you want but I am not sure, because everything looks a bit odd. Workflow attached.

 

Best

Alex

Mtvbqb
7 - Meteor

grossal,

 

Thanks for the workflow! This got me to where I wanted to be!

 

I think this is the third time you've helped me in the last three days. This is one of my first projects in Alteryx, so I'm sure I'm going about everything in a round-about way. The solutions you have given me have helped me try other workflows and become more familiar with the tools. I really appreciate the help you've given me!

grossal
15 - Aurora
15 - Aurora

I am happy to help @Mtvbqb or as Alteryx would say it "It's the Thrill of solving". I just love to solve, that's my way of getting better.

 

I got my initial jumpstart with the Interactive Lession Series to learn the basics about the tools. What I use the most when I don't know how the tools work are example workflows build into the Designer.

 

grossal_0-1585552450080.png

 

I think 80% of the tools have examples and the 80% covers 99% of my tool usage. 

 

Happy learning!

Alex

 

Labels