topic special join ? in QlikView

special join ?

Wed, 24 Jun 2009 01:01:18 GMT

Hello at All,

consider this:

A:
Load * Inline [
item , amount
AAA, 100
AAA, -100
BBB, 50
CCC, 60
];
B:
Load * Inline [
item ,qty
BBB, 20
CCC, 50
];

I need to join table "a" with table" b" and descard the
item, in table a, his sum is = to 0 (zero).
So I aspected the table excleded Item AAA (100-100).
In other words the result should be a new table contains
item BBB and CCC

thanks

Best Regards,

Slash [:)]

special join ?

johnw — Wed, 24 Jun 2009 01:17:11 GMT

C:
LOAD item, sum(amount) as sum
RESIDENT A
GROUP BY item
;
INNER JOIN (C)
LOAD item
RESIDENT C
WHERE sum<>0
;
DROP FIELD sum
;
INNER JOIN (C)
LOAD *
RESIDENT A
;
INNER JOIN (C)
LOAD *
RESIDENT B
;

special join ?

Wed, 24 Jun 2009 02:42:00 GMT

If I add this:

C:
LOAD item, sum(amount) as sum
RESIDENT A
GROUP BY item
;
INNER JOIN (C)
LOAD item
RESIDENT C
WHERE sum<>0

is correct . In fact in C table the amount was <> 0 but the item AAA does not desappears but remains.. The target is to exclude item the sum of ampunt is ?0 (AAA)

Thanks

Slash

special join ?

johnw — Wed, 24 Jun 2009 03:04:58 GMT

If you don't drop tables A and B, everything on them remains. If you're using the script I gave you, you would need to drop A and B at the end. I could probably give you a tighter script that doesn't load tables A and B in the first place, but I assumed that tables A and B were actually coming from a database or QVD, so that a more efficient solution with inline tables wouldn't necessarily accomplish what you want.

Attached is what you get if you execute your script, then my script, then drop the original tables. I think it's what you want.

special join ?

Wed, 24 Jun 2009 03:36:30 GMT

Yes you all right.. I add yjis:

Drop table A;

and now it works!

the code correct:

A:
Load * Inline [
item , amount
AAA, 100
AAA, -100
BBB, 50
CCC, 60
];
B:
Load * Inline [
item ,qty
BBB, 20
CCC, 50
];

C:
Load
item,
sum(amount) as sum
resident A
group by item;

Inner join (C) load
item
resident C
where sum <>0 ;

Drop table A;

Many manu thanks!!!

Best Regards,

Slash