Too Many Joins?

[etrotter]

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.

[swuehl]

Maybe

Don't join - use Applymap instead

[etrotter]

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

[swuehl]

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

[etrotter]

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;

[swuehl]

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]

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

[etrotter]

Here is a look at my data:

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

Then I can click on the next table and Field_53 actual values are under Identifier

the two tables load seperately

[swuehl]

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.