1 Reply Latest reply: Jul 15, 2014 5:15 AM by Johan Ohlsson RSS

    Help to Test a Contract table

      Hi,

       

      I have a contract table with 18 million records where I need to flag those contracts that has been loaded incorrectly.

       

      Does someone know how to script this in a good way, please view attached Excel-file.

       

      I've started to test missing MonthsOnBook like this:

       

      MissingMOB_temp:

      LOAD     ContractNbr AS TestMissingMOBContractNbr,

               ContractNbr AS %TestMissingMOBContractNbr,

               MonthsOnBook AS MissingMOBMonthsOnBook,

               NGO AS MissingMOBNGO

      RESIDENT MONTHLY_CONTRACT_SNAPSHOT

      ORDER BY ContractNbr, MonthsOnBook;

       

      LEFT JOIN (MissingMOB_temp)

       

      LOAD  ContractNbr AS %TestMissingMOBContractNbr,

               min(MonthsOnBook) AS MinMonthsOnBook

      RESIDENT MONTHLY_CONTRACT_SNAPSHOT

      GROUP BY ContractNbr;

       

       

      MissingMOB:

      LOAD     TestMissingMOBContractNbr,

               if( MissingMOBMonthsOnBook = peek(MissingMOBMonthsOnBook) + 1, 0,1) AS FlagMissingMOB

      RESIDENT MissingMOB_temp;

       

       

      DROP TABLE MissingMOB_temp;

       

      All help is very appreciated!

       

      Br,

      Johan

        • Re: Help to Test a Contract table

          Here is what I did and it worked.

           

           

           

          MissingMOB_temp:

          LOAD     ContractNbr AS TestMissingMOBContractNbr,

                   ContractNbr AS %TestMissingMOBContractNbr,

                   MonthsOnBook AS MissingMOBMonthsOnBook,

                   NGO AS MissingMOBNGO,

                   D_SNAPSHOT_DATE_ID as TestD_SNAPSHOT_DATE_ID,

                   SumWO AS MissingSumWO,

                   PWOR AS MissingPWOR

          RESIDENT MONTHLY_CONTRACT_SNAPSHOT

          ORDER BY ContractNbr, MonthsOnBook;

           

           

          LEFT JOIN (MissingMOB_temp)

           

           

          LOAD     ContractNbr AS %TestMissingMOBContractNbr,

                   min(MonthsOnBook) AS MinMonthsOnBook,

                   max(MonthsOnBook) AS MaxMonthsOnBook,

                   max(MonthsOnBook) - min(MonthsOnBook) AS MissingDiffMaxMinMOB,

                   COUNT(ContractNbr) AS MissingCountContractNbr

          RESIDENT MONTHLY_CONTRACT_SNAPSHOT

          GROUP BY ContractNbr;

           

           

          MissingMOB:

          LOAD     TestMissingMOBContractNbr,

            TestD_SNAPSHOT_DATE_ID,

            MissingMOBNGO,

            MissingMOBMonthsOnBook,

            MinMonthsOnBook,

            MaxMonthsOnBook,

                   MissingCountContractNbr,

                   MissingDiffMaxMinMOB,

                   if(MissingCountContractNbr - MissingDiffMaxMinMOB = 1, 0,1) AS FlagMissingMOB,

                   MissingSumWO,

                   MissingPWOR,

                   if(MissingSumWO > 0 AND MissingMOBNGO > 0, 1 ,0) AS FlagNGONotZeroonSumWO,

                   if(MissingPWOR > 0 AND MissingSumWO > 0, 1, 0) AS FlagSumWONotZeroOnPWOR

          RESIDENT MissingMOB_temp;

           

           

          DROP TABLE MissingMOB_temp;