1 Reply Latest reply: Sep 20, 2013 6:35 AM by Stephen Matthews RSS

    where to put concatenate statement in script


      Hi there,

       

      I have an error with my Concatenate Statement.

       

      Below is my script:

       

      [AGREEMENTS]:

      LOAD
      AgreementNumber     as [Agreement Number],
          StatusCode      as Status,
          RegistrationNumber    as Suffix,
          CustomerReference    as [Customer Acc],
          CustomerName,
          CurrentWDV      as NBV,
          Model,
          LeaseDefinition     as [Product Type],
          LeaseDefinitNName    as [Product Description],
          Date(StartDate)     as [Start Date],
          ContractDistance,
          PeriodOfLease     as [Contract Period],
          EscalateMaintRateP    as [Escalation %],
          CompanyKey
          where StatusCode = 'L' and (LeaseDefinition='B' or LeaseDefinition='C' or LeaseDefinition='Y' or LeaseDefinition='Z');
      SQL SELECT
      AgreementNumber,
          StatusCode,
          RegistrationNumber,
          CustomerReference,
          CustomerName,
          CurrentWDV,
          Model,
          LeaseDefinition,
          LeaseDefinitNName,
          StartDate,
          ContractDistance,
          PeriodOfLease,
          EscalateMaintRateP,
          CompanyKey
      FROM "EIE_DW".dbo."FK_agree";


      LEFT JOIN (AGREEMENTS)
      //[POSTING VALUES]:
      LOAD
      AgreementNumber     as [Agreement Number],
          Period,
          Date(DueDate)     as [Instalment Date],
          Month(DueDate)     as [Month],
          Year(DueDate)     as [Year],
          RegularRentalSpli001,
          RegularRentalSpli002   as [Rental Maintenance],
          RegularRentalSpli003,
          RegularRentalSpli004,
          Budgets001      as [Maintenance Budget],
          Interest,
          Depreciation,
          CompanyKey;
      SQL SELECT
      AgreementNumber,
          Period,
          DueDate,
          RegularRentalSpli001,
          RegularRentalSpli002,
          RegularRentalSpli003,
          RegularRentalSpli004,
          Budgets001,
          Interest,
          Depreciation,
          CompanyKey
      FROM "EIE_DW".dbo."Fk_postv";


      AGREEMENTS2:
      LOAD *,
      [Maintenance Budget]/(ContractDistance/[Contract Period]) as [Maintenance rate ph]
      Resident AGREEMENTS;
      Drop Table AGREEMENTS;
      Rename Table AGREEMENTS2 TO AGREEMENTS;

      [FK TRANSACTION LOG]:
      LOAD
      AgreementNumber    as [Agreement Number],
          InvoiceOrCredit,
          InvoiceCategory,
          DRCC,
          DRExp,
          CRCC,
          CRExp,
          NLJournalType,
          TotalValue     as [Total Value],
          CompanyKey
          where InvoiceCategory = 'MISCINV';
      SQL SELECT
      AgreementNumber,
          InvoiceOrCredit,
          InvoiceCategory,
          DRCC,
          DRExp,
          CRCC,
          CRExp,
          NLJournalType,
          TotalValue,
          CompanyKey
      FROM "EIE_DW".dbo."FK_trans";

      Concatenate (AGREEMENTS) LOAD * Resident[FK TRANSACTION LOG];