Keep, Join and Applymap in 1-N scenarios. Which is correct?

[Prabhu]

Hi Everybody,

I have loaded two tables. Table3 has 1-N scenario. I need to get Product information to Budget table:

When i use right keep between the two tables, i see the following result:

When i use right join between the tables, i see the following result:

Both times, individual sales values are same. But, total sales values are different.

Which approach we should follow in business scenarios? Which one is correct?

If i use Applymap, like this

There is no Product B information is coming in the result:

Is Applymap not recommended in 1-N scenarios?

Also attached the QVW file. Please do the needful.

Regards,

Prabhakar Vammi

Message was edited by: Prabhakar Vammi

[Miguel_Angel_Baeyens]

ApplyMap() is a good alternative and in your case is not showing B because you don't have Region "South" in your INLINE "Table3" table according to your post. KEEP is the fastest option, but since it reduces data, better to be sure you are not missing anything. JOIN works pretty much as the SQL JOINs work.

EDIT: The use you are doing of ApplyMap() is n:m, because values in Region are several times on both Table3 and Budget. Perhaps you need a double ApplyMap() or a key to make it 1:n ? Still, there is no "South" so definitely the "Unknown" in the chart is correct.

[Prabhu]

Hi Miguel, Could you please tell me why is the difference in Total Sales(keep and join)? Which one we should use in real business scenarios?

[Miguel_Angel_Baeyens]

Define which business scenarios.

If you need to reduce the set of rows because those are will not being used, then use KEEP. There might be other cases where you need to use JOIN instead, and ApplyMap() is also a good idea to avoid adding rows but adding columns instead when you have this small tables with two columns (for example, key and description, client id and name, etc.).