Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Conditionally insert a new row into a data flow

Highlighted
8 - Asteroid

I have a data flow which I have sorted into a required order using the sort tool.

 

When there is a change in the value of a particular column from one row to the next, I would like to insert a new data row between the two rows and set the value of each column in the new row, for example to a fixed value or a formula using values from the previous or next rows.

 

Any ideas?

 

Thanks. Looking forward to Inspire Europe in London, September.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@PaulRB,

 

The approach that I took was to apply a DUMMY field to the incoming data and set the value to FALSE/0 as a boolean data type.  I then used a filter to capture the insertion point for the conditional records (e.g. last record of set).  I set the DUMMY value to TRUE and then unioned the data streams back together (creating extra records).  I then sort the data which causes the inserted record to appear at the proper insertion point.  After that I updated field values where  the DUMMY is true.  All is done.

 

Screen Shot 2016-07-15 at 10.19.51 AM.png

Where I inserted records, Field 1 is now the default value of 'z'.

 

Will this solve your current need?  I hope so.

 

Enjoy Inspire.  Wish I were there too.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
8 - Asteroid

Yes! Thankyou Mark. Kicking myself for not thinking of that. You saved me a lot of time. I can use multi-row formula tool to spot the breaks in the data and number the rows within each group with a new column. Then use your suggestion to generate the extra rows and sort them back into the right order before stripping off the row number column.

Highlighted
ACE Emeritus
ACE Emeritus

For this particular problem, given how the data is nicely pre-sorted and you want an extra row only when a given variable changes; it seems like you might be able to use the results of a "unique" tool on just that field, joined back to the original data set. The idea is that "unique" will give you the first instance of each new row for the field you're interested in (I called it "breakField" in the picture)... so joining (via union) those back to the original data and re-sorting should give you what you want.  You would still need to add a Multi-Row Formula to do whatever you wanted to the transition rows.

Capture.PNG

 

Labels