Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

etrotter
Contributor II

Too Many Joins?

I have a table where 9 field values are incorrect. The only place they are correct is where they have their own table so I have to load them each from 9 different tables where they are the only field and join them to the original table. My app is loading extremely slowly and I was wondering if there is a different method I could be using rather than joining them one by one.

8 Replies
MVP
MVP

Re: Too Many Joins?

etrotter
Contributor II

Re: Too Many Joins?

Do you have to use an excel file as the mapping table?

MVP
MVP

Re: Too Many Joins?

No, you can use any table (from any source, like DBMS, QVD or even INLINE).

etrotter
Contributor II

Re: Too Many Joins?

Here is my Script, I would like to replace the fields with a * to the tables below, how would I go about writing an applymap for this?

[Contracts]:

LOAD

[field_52] AS [Contract Name],

[field_53] AS [Station*],

// [field_1004] AS [Performance Measure Exempt?],

[field_55] AS [Airline/Facility*],

[field_56] AS [Services Provided*],

[field_77] AS [Station Manager*],

[field_128] AS [Contract Type*],

[field_129] AS [Fixed Component*],

[field_243] AS [Select which Airlines if Muli-airline],

[field_289] AS [Aircraft Types*],

[field_412] AS [Equipment Type],

[field_491] AS [MTD Report Date],

[field_581] AS [Hourly Positions*],

[field_596] AS [Regional Vice President*]

// [field_981] AS [RVP Comment In Contract],

// [field_999] AS [Last PM Entry Date],

// [field_1000] AS [Days Since PM],

// [field_1005_raw] AS [Status],

// [field_1019] AS [Last Hours Entry],

// [field_1020] AS [Last Revenue Entry],

// [field_1021] AS [Days Since Hours],

// [field_1022] AS [Days Since Revenue]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_records]);

[Station]:

LOAD

[identifier] AS Station

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_field_53_raw]);

[Airline/Facility]:

LOAD

[identifier_u0] AS [Airline/Facility]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_field_55_raw]);

[Services Provided]:

LOAD

[identifier_u1] AS [Services Provided]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_field_56_raw]);

[Station Manager]:

LOAD

[identifier_u2] AS [Station Manager]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_field_77_raw]);

[Contract Type]:

LOAD

[identifier_u3] AS [Contract Type]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_field_128_raw]);

[Fixed Component]:

LOAD

[identifier_u4] AS [Fixed Component]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_field_129_raw]);

[Aircraft Types]:

LOAD

[identifier_u5] AS [Aircraft Types]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_field_289_raw]);

[Hourly Positions]:

LOAD

[identifier_u6] AS [Hourly Positions]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_field_581_raw]);

[Regional Vice President]:

LOAD

[identifier_u7] AS [Regional Vice President]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_field_596_raw]);

DROP TABLE RestConnectorMasterTable;

MVP
MVP

Re: Too Many Joins?

Your mapping table input must show two fields, first field being the key for the mapping, second field the value you want to map to.

Making some assumptions, maybe like

[StationMAP]:

MAPPING

LOAD

[__FK_field_53_raw], // Key

[identifier] AS Station // Value

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_field_53_raw]);

[Contracts]:

LOAD

[field_52] AS [Contract Name],

ApplyMap('StationMAP',[field_53],'no mapping found') AS [Station*],

// [field_1004] AS [Performance Measure Exempt?],

[field_55] AS [Airline/Facility*],

[field_56] AS [Services Provided*],

[field_77] AS [Station Manager*],

[field_128] AS [Contract Type*],

[field_129] AS [Fixed Component*],

[field_243] AS [Select which Airlines if Muli-airline],

[field_289] AS [Aircraft Types*],

[field_412] AS [Equipment Type],

[field_491] AS [MTD Report Date],

[field_581] AS [Hourly Positions*],

[field_596] AS [Regional Vice President*]

// [field_981] AS [RVP Comment In Contract],

// [field_999] AS [Last PM Entry Date],

// [field_1000] AS [Days Since PM],

// [field_1005_raw] AS [Status],

// [field_1019] AS [Last Hours Entry],

// [field_1020] AS [Last Revenue Entry],

// [field_1021] AS [Days Since Hours],

// [field_1022] AS [Days Since Revenue]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_records]);

etrotter
Contributor II

Re: Too Many Joins?

It isn't working, what assumptions are you making, maybe I can verify whether or not they are correct

etrotter
Contributor II

Re: Too Many Joins?

Here is a look at my data:

In the main table called records, here are how the fields are showing up:join.PNG

Then I can click on the next table and Field_53 actual values are under Identifierjoin1.PNG

the two tables load seperately

MVP
MVP

Re: Too Many Joins?

Guess you need to load the id field of that second table and use this as key instead of [__FK_field_53_raw]

in the mapping table LOAD.

Community Browser