This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
Thanks. Looking forward to Inspire Europe in London, September.
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.
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.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
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.
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.