topic Finding Orphans in QlikView

Finding Orphans

Fri, 29 Apr 2011 21:59:05 GMT

I have two tables both have the same key field (Sales Order together with Line Number). Table 1 has Sales Order and Line number 123-1, 123-2, 123-3 and 123-4. But Table 2 has line 1 2 and 3 but not 4.

I need a script that will take the two tables and the result is only items in table 2 that are not in table 1 (ie I only want to see 123-4)

How can I do this?

Thanks,

Stephen

Finding Orphans

Fri, 29 Apr 2011 22:17:17 GMT

if the values are the same you could use not exists(key-1, key2) when loading the second table .

Finding Orphans

Fri, 29 Apr 2011 22:23:01 GMT

Of course. have used before. It must be Friday! Thanks,

Finding Orphans

Fri, 29 Apr 2011 22:23:32 GMT

Ok, I missunderstood a little the question.

You still can use not exists but you'll have to load the second table again with not exists(key-2, key-1).

Hope to be right this time.

Finding Orphans

Fri, 29 Apr 2011 22:58:34 GMT

Carlos, hope you are still around. I am having trouble the resident part. Can you create a small example?

Thanks,
Stephen

Finding Orphans

qw_johan — Sat, 30 Apr 2011 08:55:22 GMT

Hi,

Try this...

Load * Resident Table1 where not exists(Table2.KEY, Table1.KEY);

Including example application.

Good luck,

Johan

Finding Orphans

Mon, 02 May 2011 15:37:57 GMT

Hi,

You don't have to use resident unless you have have previously loaded the second table and you want to get your data from that table in memory.

You simply have to make sure that the first field to be used in the Exists( ) function has already been loaded when you use it.

Since you want the values in table 2 that don't exist in table 1, you'll have to do the next:

TABLE 1:

LOAD *

FROM OR RESIDENT WHATEVER YOUR SOURCE IS

(This includes the field Sales Order Line Number of the first table, suppose it is called Key1).

TABLE 2:

LOAD *

FROM OR RESIDENT WHATEVER YOUR SOURCE IS

(This includes the field Sales Order Line Number of the second table, suppose it is called Key2).

WHERE NOT EXISTS(Key1,Key2 );

So, the second table will only load the records that have a Sales Order Line Number that don't exist in Table 1.

Hope this helps!