8 Replies Latest reply: Sep 18, 2017 2:56 PM by Stefan Wühl RSS

    Too Many Joins?

    Erica Trotter

      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.

        • Re: Too Many Joins?
          Erica Trotter

          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;

            • Re: Too Many Joins?
              Stefan Wühl

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