Copying a row based on a certain Criteria

[Idyllic_Data_Geek]

Attached file shows the same input and the expected output. Criteria is if column 6 = 'And' then copy the entire row with all the same column values except for column 7,8 and New column. The copied row will have the column 8 under column 7 and new column value = 2 which signifies that it is the second person on the account.

Thanks for your help in advance!

Cheers!

[Qiu]

@Idyllic_Data_Geek 
I think we can do it by a Transpose tool and a Tile tool as below.

We will bring the column of names vertically then use a Tile tool to give ID per group.

[binuacs · Accepted solution]

@Idyllic_Data_Geek One way of dong this

[Idyllic_Data_Geek]

Thank you. Can you please explain what is this calculation doing? IIF(isNull([Row-1:1]),1,[Row-1:New Column]+1)

[ddiesel]

Hi @Idyllic_Data_Geek!

I replied to this on your other post.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Remove-all-special-characters-punctuat...

It looks like the formula you are referencing is in a Multi-Row formula tool.

Translation:

If column "1" is null in the row above the current row, then the value of 1 is returned. Otherwise, "New Column" plus 1 from the row above the current row is returned. 

Sounds like the new column is creating a RecordID. Is it working for you? Looks odd to me.

If you need help with this formula, I recommend opening a new topic.

[Idyllic_Data_Geek]

I'm unable to make this formula work for the actual data and I'm getting confused by column name 1. Let's if the first column name was Serial # instead of 1 then what will be the calculation? Many thanks for your help in advance!

[gabrielvilella]

Are you able to provide sample data and the expected outcome? 

[ddiesel · Accepted solution]

Hi @Idyllic_Data_Geek!

If the column name is "Serial #" then the formula would look like this:

IIF(isNull([Row-1:Serial #]),1,[Row-1:New Column]+1)

Pay attention to the data type for "Serial #" and "New Column". They both must be numeric for this formula to work.

As @gabrielvilella mentioned, if you upload some dummy data and the desired output, we can help you troubleshoot it.

Thanks,
Deb