Pulling specific values into a net column

[peter-gillespie]

Hi folks,

I've had a search around and can't find an answer (or can't think of the correct search term to find the answer, more likely....).

I have a dataset:

Name	Value
Adjustment 1	123
Adjustment 2	456
Adjustment 3	789
Adjustment 1 net	100
Adjustment 2 net	200
Adjustment 3 net	300

I somehow need to make it look like this:

Name	Value	Net value
Adjustment 1	123	100
Adjustment 2	456	200
Adjustment 3	789	300

Essentially, the net values are currently stored in additional rows and need to be shifted up to a new column and placed on the same row and their corresponding gross value. The adjustment names will be the same every time so I don't need it to be dynamic.

Is this possible in Alteryx or do I need to go back to the datasource?

Thanks in advance!

[Emmanuel_G · Accepted solution]

@peter-gillespie 

Yes it is possible in Alteryx.

Find in attachement the way of doing this.

Let us know if it works as you want.

[davidskaife]

Hi @peter-gillespie 

This should give you what you need:

Split the stream between those containing the word net (with a space in front), using the Dynamic Rename tool add Net to the value header, strip out net from the name, and join back together using the Find & Replace tool.

[peter-gillespie]

Thanks both - really helpful!