Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 [:)]
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
;
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
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.
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