Entry must be replaced form another entry

[Massi1]

Hello, 

i have a big Table. In this table I have multiple colums. 

I have a Colum "Account Number". These Number can have 2 rows or 7 rows or 100 rows. 

Then I have a Colum "PRC" In each Account Number is a PRC. The problem is that every "PRC" over 600 must be replaced. 

They must replaced by the number under 600 in the Account number. 

Example I have a Account Number with 6 rows. 

The 6 rows have two different PRC. One is 100 and the other is 610. Then the 610 must be replaced by the same Account Number PRC. In this case the 100. 

If I have three or more PRCs then the 600 must by replaced from this PRC, who have the same Amount in in colum "Amount"

Can u help? 

Thanks  

[vizAlter]

@Massi1 — Could you please attach a sample file or dummy records?

[Massi1]

Hey , 

thanks for the reply. 

here is dummy file but thats very simplify presentation of the datas. 

I have 35k Records. 

[hroderick-thr]

Think it through buddy 🙂

1. Input the data

2. Use a filter to split into PRC >= 600, giving you separate true and false datasets

3. Join the true and false datasets on the fields that must match. Put true on the right and false to the left.

(You may need 2 joins, first matching account and amounts, next with matching accounts only)

4. This gives you 3 datasets Right, Join, and Left

5. On the Join dataset, use a formula tool to recalculate the PRC value that is >= 600

...do what you need with the data after that

Your rules remain ambiguous:

• What if you have the 5 rows with the same account and same amount and different prices, all under 600, and a 6th row with same account and amount and price over 600, which of the 5 PRC do you use? Average? Max? Min?
• What if your PRC is exactly 600, do you replace it?
• What if your PRC is 601 but no other rows have the same account?

[vizAlter · Accepted solution]

@Massi1 -- Here is my take, please review the attached.

I am using "Find Replace" tool which is workable on Strings only, later, converting into the correct  data type.

[Massi1]

Thanks for your replys. 

IT helps me a lot. I change a lil bit but now it works. 

[vizAlter]

Good!

If this post assists in resolving the question, please mark it "Like", or "Solved" if it resolves the question. This will help other users find the same answer/resolution.  Thank you.