Help to Test a Contract table

Mon, 14 Jul 2014 10:06:15 GMT

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

Tue, 15 Jul 2014 09:15:21 GMT

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;

topic Re: Help to Test a Contract table in QlikView

Help to Test a Contract table

Re: Help to Test a Contract table