Mapping Issue

[asknyldz]

Hello everyone,

I want to map two table.

1. Table (MercuryData) values are "SEGMENT, BUSINESS UNIT, SPECIALTY"

2. Table (MarshForce) value is "Team"

So this is the result. But i want to map "Team" together. What is the issue?

Team	Forecast	Actual
Claims	500,518 $	0 $
Commercial P&C	486,341 $	0 $
Consumer P&C	496,060 $	0 $
Corporate P&C	6,081,648 $	0 $
Employee Benefits	4,168,092 $	0 $
Fin-Pro	3,460,869 $	0 $
Marine	35,000 $	0 $
MRC	7,335,256 $	0 $
RM Industrial P&C	2,255,815 $	0 $
RM Industrial Specialties P&C	5,710,209 $	0 $
RM MCS P&C	301,975 $	0 $
Surety	555,000 $	0 $
Trade Credit	1,710,705 $	0 $
-	0 $	27,999,304 $

This is the script,

LOAD
[SEGMENT],
UPPER([SEGMENT]) as SEGMENT_SA,
[BUSINESS UNIT],
UPPER([BUSINESS UNIT]) as BU_SA,
[CLIENT],
[CLIENT TYPE],
[CLIENT EXECUTIVE],
[CLIENT ADVISORY],
[ACCOUNT EXECUTIVE],
[SPECIALTY],
UPPER([SPECIALTY]) as SPECIALTY_SA,
If([SPECIALTY]='Property','P&C',If([SPECIALTY]='Casualty','P&C',If([SPECIALTY]='Multiline','P&C',[SPECIALTY]))) As SPECIALTY2,
[BUSINESS],
[REGION],
UPPER([REGION]) as REGION_SA,
[TYPE],
[MONTH],
[AMOUNT]
FROM
[MercuryData.xlsx]
(ooxml, embedded labels, table is [MercuryData]);

Concatenate

LOAD
[Opportunity Value (converted)],
[MF_Business Unit],
[Product Line],
[Line of Business],
[Line of Coverage],
[Weighted Amount (converted)],
[Close Month] as [MONTH],
[Funnel Stage],
[Team]
FROM
[MarshForce.xlsx]
(ooxml, embedded labels, table is [MFData]);

Concatenate

LOAD
[SEGMENT],
UPPER([SEGMENT]) as SEGMENT_SA,
[BUSINESS UNIT],
UPPER([BUSINESS UNIT]) as BU_SA,
[SPECIALTY],
UPPER([SPECIALTY]) as SPECIALTY_SA,
[Team],
UPPER([Team]) as Team_SA
FROM
[MarshForce.xlsx]
(ooxml, embedded labels, table is [MappingMer]);

This is the mapping table;

SEGMENT	BUSINESS UNIT	SPECIALTY	Team
Commercial	Bireysel	Property	Commercial P&C
Commercial	Kurumsal	Property	Commercial P&C
Consumer	Consumer	Property	Consumer P&C
Corporate	Corporate	Property	Corporate P&C
Other	Other	Property	Other
Risk Management	Energy	Property	RM Industrial Specialties P&C
Risk Management	Industrial	Property	RM Industrial P&C
Risk Management	Infrastructure	Property	RM Industrial Specialties P&C
Risk Management	Mcs	Property	RM MCS P&C
Risk Management	Power-Utilities	Property	RM Industrial Specialties P&C
Commercial	Bireysel	Casualty	Commercial P&C
Commercial	Kurumsal	Casualty	Commercial P&C
Consumer	Consumer	Casualty	Consumer P&C
Corporate	Corporate	Casualty	Corporate P&C

Thank you all.

[StarinieriG]

Hi

Try to join the tables, instead of using concatenate

[asknyldz]

Thanks for your answer.

Now I'm joinning 3 table but then there is missing data. I see only one rows value in totals. You can below data and script.

MercuryData:

SEGMENT	BUSINESS UNIT	SPECIALTY	TYPE	MONTH	AMOUNT
Risk Management	Mcs	Property	ACTUAL	Apr-19	600
Risk Management	Mcs	Property	ACTUAL	Feb-19	600

Script;

LOAD
[SEGMENT],
[BUSINESS UNIT],
[SPECIALTY],
[TYPE],
[MONTH],
[AMOUNT]
FROM
[MercuryData.xlsx]
(ooxml, embedded labels, table is [MercuryData]);

JOIN

LOAD
[SEGMENT],
[BUSINESS UNIT],
[SPECIALTY],
[Team],
UPPER([Team]) as Team_SA
FROM
[MarshForce.xlsx]
(ooxml, embedded labels, table is [MappingMer]);

LOAD
[Opportunity Value (converted)],
[MF_Business Unit],
[Product Line],
[Line of Business],
[Line of Coverage],
[Weighted Amount (converted)],
[Close Month] as [MONTH],
[Funnel Stage],
[Team]
FROM
[MarshForce.xlsx]
(ooxml, embedded labels, table is [MFData]);

[Arthur_Fong]

You missed out a join statement at here:

join

LOAD
[Opportunity Value (converted)],
[MF_Business Unit],
[Product Line],
[Line of Business],
[Line of Coverage],
[Weighted Amount (converted)],
[Close Month] as [MONTH],
[Funnel Stage],
[Team]
FROM
[MarshForce.xlsx]
(ooxml, embedded labels, table is [MFData]);

[Brett_Bleess]

Askin, did Arthur's last post get you what you needed to have a working solution?  If so, please be sure to use the Accept as Solution button on the posts that helped you get what you needed to give Arthur credit for the help and to let others know what actually worked.  If you did something different, please consider posting that and then you can mark that as the solution.  If you are still working on things, leave an update with where things are, and we'll see if we can get you the rest of the way.

Regards,
Brett