topic Re: Joinning 3 Tables and missing data in QlikView

Joinning 3 Tables and missing data

asknyldz — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone.

I'm joinning 3 table and 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]);

Re: Joinning 3 Tables and missing data

Arthur_Fong — Thu, 12 Sep 2019 06:56:55 GMT

Can you provide a sample qvw?

Re: Joinning 3 Tables and missing data

Brett_Bleess — Tue, 24 Sep 2019 16:26:31 GMT

Askin, if you are not able to attach QVW as Arthur had requested, this is going to be tricky at best to try t help. About the best I can offer is some troubleshooting advice, which is to load each of those tables separately in a new QVW file, one QVW for each source, so you can check the contents of the load to ensure everything you expect to be there is there. That way you know the source info is good and the issue is in the Join etc.

Here is a Design Blog post related to Joins, might be of some use as well:

https://community.qlik.com/t5/Qlik-Design-Blog/To-Join-or-not-to-Join/ba-p/1463102

About the best I have for the moment to try to help you move things forward.

Regards,
Brett