Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Do you have to use an excel file as the mapping table?
No, you can use any table (from any source, like DBMS, QVD or even INLINE).
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;
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]);
It isn't working, what assumptions are you making, maybe I can verify whether or not they are correct
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
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.