Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

asknyldz
New Contributor III

Mapping Issue

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?

TeamForecastActual
Claims500,518 $0 $
Commercial P&C486,341 $0 $
Consumer P&C496,060 $0 $
Corporate P&C6,081,648 $0 $
Employee Benefits4,168,092 $0 $
Fin-Pro3,460,869 $0 $
Marine35,000 $0 $
MRC7,335,256 $0 $
RM Industrial P&C2,255,815 $0 $
RM Industrial Specialties P&C5,710,209 $0 $
RM MCS P&C301,975 $0 $
Surety555,000 $0 $
Trade Credit1,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;

SEGMENTBUSINESS UNITSPECIALTYTeam
CommercialBireyselPropertyCommercial P&C
CommercialKurumsalPropertyCommercial P&C
ConsumerConsumerPropertyConsumer P&C
CorporateCorporatePropertyCorporate P&C
OtherOtherPropertyOther
Risk ManagementEnergyPropertyRM Industrial Specialties P&C
Risk ManagementIndustrialPropertyRM Industrial P&C
Risk ManagementInfrastructurePropertyRM Industrial Specialties P&C
Risk ManagementMcsPropertyRM MCS P&C
Risk ManagementPower-UtilitiesPropertyRM Industrial Specialties P&C
CommercialBireyselCasualtyCommercial P&C
CommercialKurumsalCasualtyCommercial P&C
ConsumerConsumerCasualtyConsumer P&C
CorporateCorporateCasualtyCorporate P&C

 

Thank you all.

Labels (1)
3 Replies
Partner
Partner

Re: Mapping Issue

Hi

Try to join the tables, instead of using concatenate

asknyldz
New Contributor III

Re: Mapping Issue

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:

SEGMENTBUSINESS UNITSPECIALTYTYPEMONTHAMOUNT
Risk ManagementMcsPropertyACTUALApr-19600
Risk ManagementMcsPropertyACTUALFeb-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]);

Partner
Partner

Re: Mapping Issue

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]);