7 Replies Latest reply: Jun 21, 2013 3:37 AM by Mohit Sharma RSS

    Nested Preceding load ?

    Brian Garside

      Sample script for my Preceding load. Im tryin to do some Apply mapping on just these fields (not finished) while the rest in the SQL Select I just need to load as is.  Right now it only loads the ones in the precdieng load even though I have the LOAD *;  at the end to pull the rest in?

       

      What Im a doign wrong do I still need to use Resident ?

       

       

      [OpportunityCustomTabFields]:

      LOAD

      ApplyMap('MAP_EmployeeToFullName',CustProposalCoordinator, Null() ) AS CustProposalCoordinator,
      CustPastPerformanceLead,
      ApplyMap('MAP_EmployeeToFullName',CustProgramManager, Null() ) AS CustProgramManager,
      CustStaffingPlanLead,
      CustBookBoss,
      CustWriter1,
      CustWriter2,
      CustWriter3,
      CustWriter4;

       

      LOAD *;


      SQL SELECT custApprovedBy,
      custAwardDate,
      custAwardDate3B,
      CustBackupBusinessSector,
      CustBackupCOE,
      CustBackupServiceSector,
      custBAFODate,
      custBidDecision,
      custBiddersConference,
      custBlackTeamReviewDate,
      custBlueTeamReviewDate,
      custBPNumber,
      custCaptureStrategy,
      CustCDBSubmit,
      CASE WHEN CustClassified1 Is Null THEN 'Not Set' ELSE CustClassified1 END AS CustClassified1,
      custClientProgramManager,
      CASE WHEN custCOE Is Null THEN '-' ELSE custCOE END AS custCOE,
        .....

        • Re: Nested Preceding load ?
          Gysbert Wassenaar

          Your top load statement doesn't load all fields, but only those you explicitly listed. The second load *; lines is not necessary. It doesn't add do anything useful. It only passes the results from the sql load to the top load. But you can leave it out. It's not necessary. See this blog post.

           

          What you do need to do is list all the fields you want in the final table in the top load statement.

            • Re: Nested Preceding load ?
              Brian Garside

              Ok, yep. Was hoping to not to list all the fields explicitly. But makes sense if there is no alternative.

               

              Thanks for the quick reply!

                • Re: Nested Preceding load ?
                  whiteline _

                  You could use both asterisk and explicetly defined feilds:

                  LOAD

                       *,

                       feld1,

                       field2;

                  SELECT ...;

                   

                  Of couse, there should be no duplicates.

                    • Re: Nested Preceding load ?
                      Brian Garside

                      Nice, I will have to give it a try. I guess I had the LOAD * , in the wrong place at bottom instead of top.

                      • Re: Nested Preceding load ?
                        Brian Garside

                        Duplicates in the preceding or SQL Select?  still getting an error....OLEDB Read error

                         

                        [OpportunityCustomTabFields]:

                         

                         

                        LOAD //Preceding Load used for ETL, pulls from SQL Select below

                         

                                  * ,          //Load everything, but Do this ApplyMap first

                         

                            ApplyMap('MAP_EmployeeToFullName',CustProposalCoordinator, Null() ) AS CustProposalCoordinator,

                            ApplyMap('MAP_EmployeeToFullName',CustPastPerformanceLead, Null() ) AS CustPastPerformanceLead,

                            ApplyMap('MAP_EmployeeToFullName',CustProgramManager, Null() ) AS CustProgramManager,

                               date(floor(custProposalDueDate)) as custProposalDueDate;

                         

                         

                        SQL SELECT custApprovedBy,

                            custAwardDate,

                            custAwardDate3B,

                            CustBackupBusinessSector,

                            CustBackupCOE,

                            CustBackupServiceSector,

                            custBAFODate,

                            custBidDecision,

                            custBiddersConference,

                            custBlackTeamReviewDate,

                          • Re: Nested Preceding load ?
                            whiteline _

                            In the preceding.

                             

                            Imagine than QV replaces asterisk with the list of all fields. Then you add:

                            ApplyMap('MAP_EmployeeToFullName',CustProposalCoordinator, Null() ) AS CustProposalCoordinator,

                            But there is a field with similar name already.

                             

                            You have either to:

                                 1) Use asterisk and different names for mapped fields, then drop unnecessary fields.

                            or

                                 2) List and name all fields explicitly.

                    • Re: Nested Preceding load ?
                      Mohit Sharma

                      try simply like this

                      TB_M_LEAVEPLANT_TYPE_MASTER:

                      LOAD LeaveTypeID,

                          LeaveType,

                          NoOfDaysEntitled,

                          DaysAccumulatable,

                          EncashmentRule,

                          HalfDayPermissible,

                          BaseLimitApplied,

                          LeaveTypeSName,

                          LeaveGrpID,

                          MinDeductedDays,

                          SaviorLeaveCode;

                      SQL SELECT *

                      FROM "IGL_APS".dbo."TB_M_LEAVEPLANT_TYPE_MASTER";

                       

                      [OpportunityCustomTabFields]:
                      //Here you load all fields which is working as precedence load while fetchind data from table it gives you an option at last right corner for select precedence load when you tick on automatically yuor all fields come from your SQL Database as above i mentioned the example
                      LOAD

                      custApprovedBy,

                      custAwardDate,

                      custAwardDate3B,

                      CustBackupBusinessSector,

                      CustBackupCOE,

                      CustBackupServiceSector,

                      custBAFODate,

                      custBidDecision,

                      custBiddersConference,

                      custBlackTeamReviewDate,

                      custBlueTeamReviewDate,

                      custBPNumber,

                      custCaptureStrategy,

                      CustCDBSubmit

                      if(isnull(CustClassified1),'Not Set',CustClassified1) as CustClassified1,

                      if(isnull(custCOE),'-',custCOE) as custCOE;

                      ApplyMap('MAP_EmployeeToFullName',CustProposalCoordinator, Null() ) ASCustProposalCoordinator,
                      CustPastPerformanceLead,
                      ApplyMap('MAP_EmployeeToFullName',CustProgramManager, Null() ) ASCustProgramManager,
                      CustStaffingPlanLead,
                      CustBookBoss,
                      CustWriter1,
                      CustWriter2,
                      CustWriter3,
                      CustWriter4;

                      SQL SELECT *

                      from tablename;