topic best solution for finding missing record in QlikView

best solution for finding missing record

Wed, 20 Oct 2010 09:10:43 GMT

Dear all

I have two table linked for a key field.

i would like to know which is the best solution for count how many record are not present in a table.

Best Regards

Riccardo

best solution for finding missing record

Wed, 20 Oct 2010 09:59:58 GMT

Hi Cattermol,

Try looking at Document Properties > Table Tab.

But if your looking for the excluded from selections you can try this code:


Count( {1-$} FieldName)

This will count the excluded values.

Regards

best solution for finding missing record

Wed, 20 Oct 2010 10:19:41 GMT

Dear Ronald

i have a situation like this (after a outer join from table Oracle and table Archidoc) :

KEY	ORACLE	ARCHIDOC
431346	431346	431346
431347	431347	431347
431386	431386	431386
431401	431401
431431		431431
431459	431459	431459

I have to count how many record are in column ORACLE that are not in column ARCHIDOC.

in this example a have 1 record (431401)

Thank you very much

Riccardo

best solution for finding missing record

syed_muzammil — Wed, 20 Oct 2010 10:22:05 GMT

Hi,

Do you mean the count of records in table1 which are not present in table2?

Then you can jus have a different name for the KEY in each table .

Eg:

Tab1:

Load

Key,

Key as Key

from....;

tab2:

Load Key,

Key as Key2

from ....,

Then in the front end you can jus do a Count(Key1)-Count(Key2) which will give you the count of records not present in your table2 .

Regards,

Syed Muzammil.

best solution for finding missing record

syed_muzammil — Wed, 20 Oct 2010 10:27:51 GMT

Hi,

I didn't see your second reply. After Outer Join jus add another field

if (len(ACHIDOC)=0 ,1,0) as Flag

then do sum(Flag) in the front end , you will get the count of the missing records.

best solution for finding missing record

Wed, 20 Oct 2010 10:35:22 GMT

thank's to all for your help.

Often the best solution is the simplest.

Riccardo