Missing rows between 2 QVD's

[Anonymous]

Hi

I have 2 QVD's, one from a Full data load QVD-FULL and the other which has been incrementally loaded QVD-INC.  In theory they should be identical, but unfortunately they have different numbers of rows.

They both have a unique ID field called say ID.

What would be an approach to find out :

• ID values in FULL and not in INC
• ID values in INC and not in FULL

Here are a couple of small sample Inline Loads of test data.  In reality the QVD's have a few millions rows, but I could easily trim them down to a few hundred thousand rows and the comparison would still be meaningful.

FULL:

LOAD * INLINE [

    ID

    1

    2

    3

    8

    9

];

INC:

LOAD * INLINE [

    ID

    1

    4

    5

    6

    7

    9

];

Best Regards,     Bill

[#2 · Accepted solution]

You can Try with Exists function:

FUL:

LOAD ID FROM FULL.QVD;

NOT_IN_FUL:

LOAD ID FROM INC.QVD WHERE NOT EXISTS (ID);

Another Method: Join the 2 tables with Flag:

TEMP:

LOAD ID, 'F' AS F_FLAG FROM FULL.QVD;

JOIN

LOAD ID,'I' AS I_FLAG FROM INC.QVD;

NOT_IN_FULL:

LOAD ID

RESIDENT TEMP WHERE F_FLAG = NULL();

[Anonymous]

Dathu

I prefer your 2nd method, I did a couple of tweaks, ended up with this and it tests fine.

TEMP:

LOAD * INLINE [

    ID , F_FLAG

    1 , 'F'

    2 , 'F'

    3 , 'F'

    8 , 'F'

    9 , 'F'

];

outer JOIN

LOAD * INLINE [

    ID , I_FLAG

    1 , 'I'

    4 , 'I'

    5 , 'I'

    6 , 'I'

    7 , 'I'

    9 , 'I'

];

qualify * ;

NOT_IN_FULL:

LOAD *

RESIDENT TEMP WHERE not F_FLAG = 'F' ;

I just need now to run it against few large production QVD's.

Many Thanks,     Bill

[#4]

If you are looking only ID's , user second method with qvd optimized load.