New column based on conditions

[pranee_007]

Inputfile1

Z	A	B	C
1	Fund1	Entity1
2	Fund2	Entity2
3	Fund3	Entity3
4	Fund5	Entity1
5	Fund4	Entity4

 Inputfile 2

Z	X	Y
1	a	h
2	s	y
3	d	r
4	f	j
5	d	e
6	f	g

Team,

I am looking to create new column as shown above.

Conditions:

1. If (A=fund1 or B=Entity1 or B=entity2) then fill column c with value 'zero'

2. If(B=entity3 or B=entity4) then value of column c will be the lookup value of corresponding value of its Z in source2 and returns the corresponding value from column y.

Example: If B= entity3 then z value is 3. so it checks for this z in source 2 and take the value of column y corresponding to z=3 which is 'r' and keeps it in column c of first table.

Please help me how to build this logic.

[RolandSchubert · Accepted solution]

Hi @pranee_007 ,

you need a Join tool and a Formula tool to build this logic.

Join inputfile1 and inputfile2 on field [Z]. Then add a Formula tool with a formula to calculate column [C]:
IF [A] = 'Fund1' OR [B] IN (Entity1, Entity2) THEN
   0
ELSE
  [Y]    <- the column you added by the join before
ENDIF
I've added a sample workflow using this approach.

Another option would be to use a Fitler tool to split between the rows with Fund1 or Entity1/Entity2 and the other rows.
You would join only the "other rows" with inputfile2, use the formula to fill field [C] and use Union to reunion all rows.

Best regards

Roland

[JoeS]

Hi @pranee_007 

I have built a workflow to follow you logic.

I used a filter tool and formula tool for part 1.

Then joined the datasets together for part 2 and replace column c from column y, by renaming in the join tool.

Then unioned the data back together.

Edit: Doh, beaten to it by the time I wrote my post and solution. Similar stuff done though 🙂