Help to Test a Contract table

[#1]

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

[#2 · Accepted solution]

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;