topic Re: Searching for one rogue record ... in QlikView

Searching for one rogue record ...

datanibbler — Thu, 02 Jul 2015 09:30:38 GMT

Hi,

this is actually quite simple (I guess), I just cannot think of the right way to do it:

- I have two tables which should have the exact same nr. of records

- When I don't select anything, table_B has exactly ONE record more than table_A ...

<=> When I select on one field (which actually has only one value due to a filter in the script), the numbers match exactly.

=> How can I find out which is this one record? The "select excluded" in the context_menu doesn't work - the field actually has only one value, so I don't see what the difference is - well, it would appear that a JOIN prior to that failed for one record, thus it has no value ...

Can you help me there?

Thanks a lot!

Re: Searching for one rogue record ...

marcus_sommer — Thu, 02 Jul 2015 09:34:09 GMT

Hi DataNibbler,

I think you will need a counter within your tables: Counters in the Load.

- Marcus

Re: Searching for one rogue record ...

datanibbler — Thu, 02 Jul 2015 10:46:07 GMT

Hi Marcus,

a counter is always a good idea. And then?

I just try loading without that filter - then that field that I join into the primary table should have several values, one of them should be a NULL or empty or something ...

Re: Searching for one rogue record ...

datanibbler — Thu, 02 Jul 2015 10:52:31 GMT

OK.

I have now found out that in my table_B, I have exactly ONE record with one of the GROUP_NAME values that I have filtered out - but only out of table_A because my LEFT KEEP() (or EXISTS()) comes earlier. So I'll just have to change the order of execution of a few steps in the script, and that should be it.

So sweating does help 😉 (well, it always helps with Chuck Norris - but I hate this heat still ...)

Let's see ...

Re: Searching for one rogue record ...

marcus_sommer — Thu, 02 Jul 2015 10:54:21 GMT

Hi DataNibbler,

when I have such unexpected results and couldn't find the (most obvious) reasons I use such counters (most recno and rowno) within the tables and load the origin-tables as qualified tables - and then you will find which records are missing.

- Marcus