Multi row calculation

[Marina_Kelner]

Hello Everyone,

I am desperately looking for help with multi-row calculation (it is always giving me a headache). 

Fields with "no color" are the input file fields, where as columns in "pink" are calculated fields based on a logic. 

Column E = brings Column D values, based on Column B values that are found in Column A. For example (as in xls), first 3 rows have Column B = "Value I", we need to search Column A for "Value I" and then place Column D values into Column E (first 3 rows respectively).  We do need to make sure that Reporting Period is taken into consideration. 

Column F = easy, and is a difference between columns E and F (no need help there)

How do I build a logic to do so?

thank you!

Marina

[binu_acs]

@Marina_Kelner One way of doing this with the join tool

[Raj · Accepted solution]

@Marina_Kelner 
please find attached
hope this helps.
mark done if solved.

[Marina_Kelner]

Dear Raj and Binuacs, thanks so much for your solutions!  

I tested both and both worked!  Binuacs solution does brings out few additional records (duplicates) since my record count is larger in the output.  thank you!

[Marina_Kelner]

Hello @Raj , 

After careful examination (the original file consists of over 7000 records and over 25 metrics, that i would be applying this solution too), I found little discrepancies. 

It has something to do with a date.   My example didn't show that months might not be the same between Value field and Value Universe field and thus it looks like in some cases where the months are different, it picks up the values from the earlier month than the month it should be picking it up from.  attaching the image.. let me know if that is clear.  thank you!

[Raj]

@Marina_Kelner  in my soluion i did not include the data parameter.
if you are looking for same date then we need to update the flow.

let me know if help required.

[Marina_Kelner]

@raj

yes, i need a solution please.. i couldn't figure it out. 

it works great when the number of records match (the same number of Value field records and the same number of Universe Value Records), however if there are extra records with another date, it doesn't pick up the values properly.

thank you!

[Marina_Kelner]

Hi @Raj , please let me know if you can assist, and if not, i will repost the problem! thanks so much for everything so far!

[Raj]

@Marina_Kelner 
workflow  updated
please find attached.