topic Re: Missing rows between 2 QVD's in QlikView

Missing rows between 2 QVD's

Anonymous — Wed, 20 Nov 2013 17:21:30 GMT

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 [

];

INC:

LOAD * INLINE [

];

Best Regards, Bill

Re: Missing rows between 2 QVD's

Wed, 20 Nov 2013 17:30:15 GMT

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();

Re: Re: Missing rows between 2 QVD's

Anonymous — Wed, 20 Nov 2013 17:45:19 GMT

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

Re: Missing rows between 2 QVD's

Wed, 20 Nov 2013 17:50:24 GMT

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