Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
Try to join the tables, instead of using concatenate
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]);
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]);
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