Replace 0 with a value

[Alharbia]

Hello,

I have this question and I hope I find the solution for it 

I have a very large data with the same problem.

see the example table below

ID	Name	Basic salary	file type	Allowance
1234567	Jack	1,000	Payroll
1234567	Jack	0	Other	300
5123333	Tom	2,000	Payroll
5123333	Tom	0	Other	400

I have a data for the same person in two row the basic salary one with a value and the other one Zero. I want to replace the zero with the 1,000.

How can I do that in Alteryx.

Thanks in advance for your cooperation.

[flying008]

Hi, @Alharbia 

1- Formula of [ Basic salary ]:

iif([Basic salary]=0, 1000,[Basic salary])

2- Multi-Row Formula for [ Basic salary ] :

a- Group by [Name]

b- Expression:

iif(tostring([Basic salary]) = "0",[Row-1:Basic salary],[Basic salary])

[DataNath]

Hey @Alharbia, are you wanting to do this for all names rather than just Jack? If so you could maybe use an approach like so, where we can use the Multi-Row Formula like so to just autofill down from the previous row:

[Alharbia]

Thank you you have been very helpful 

but how can I solve the problem if my data is not organized  or sorted by name or even ID  

My Data is hugged  

[Jonathan-Sherman · Accepted solution]

Hi @Alharbia,

I would go down the route of determining the correct pay per ID and joining it back to the original dataset and overriding the 0 values:

I've attached an example workflow!

Kind regards,

Jonathan

[Alharbia]

Thank you All

It works great