Mimic Vlookup in Dataset?

deona720

As the title says, I am trying to mimic a vlookup on a dataset. I am using an ETL that is connecting different membership logs from SF. Basically every time a member leaves or joins it creates a new log. On one dashboard we are looking at data based on all the members who joined this year but also left this same time frame. So my dashboard data is all filtered by join logs essentially, since it is only join logs they don't include the termination reasons. How can I get the termination reason to apply to all member logs with the same ID? (Would be ideal if I could make a new column in a view data set)

Data looks like this:

Member ID	Log Status	Termination Reason
1234	Join
1234	Leave	Too expensive
5678	Join
5678	Leave	Found alternative solution
1111	Join
1111	Leave	Uninterested

And I want it to look like this:

Member ID	Log Status	Termination Reason
1234	Join	Too expensive
1234	Leave	Too expensive
5678	Join	Found alternative solution
5678	Leave	Found alternative solution
1111	Join	Uninterested
1111	Leave	Uninterested

MichelleH

@deona720 Joins are the ETL equivalent of vlookup, with a few minor differences. You can read more about joins in this Knowledge Base article. In this case I would recommend creating a branch of your dataflow that filters your input dataset on Log Status = Leave so that you have one row per Member ID that includes the Termination Reason. Then use a left join with the original input on the left and the filtered data on the right, using Member ID as your join field. Drop the Termination Reason from the left table and the Log Status from the right table to mimic the output from your example.

MichelleH

@deona720 Joins are the ETL equivalent of vlookup, with a few minor differences. You can read more about joins in this Knowledge Base article. In this case I would recommend creating a branch of your dataflow that filters your input dataset on Log Status = Leave so that you have one row per Member ID that includes the Termination Reason. Then use a left join with the original input on the left and the filtered data on the right, using Member ID as your join field. Drop the Termination Reason from the left table and the Log Status from the right table to mimic the output from your example.