Join help

[paulwalker]

HI Community,

I have 2 tables, i have more than one common field. but i have to do left join with one key only.

here i want to left join with CatID.

Sample Example:

LOAD * INLINE [

    CatID, costcenter, amount

    1001, ABC, 300

    1001, BCA, 120

    1001, MKL, 900

    1000, jkl, 90

    1000, oip, 980

    1011, LPO, 650

    1011, POL, 320

];

left join

LOAD * INLINE [

    CatID, costcenter, Chours, Wamount

    1001, ABC, 10, 100

    1001, ACB, 2, 200

    1111, KLJ, 7, 300

    1000, jkl, 12, 400

    1001, oip, 17, 500

    1111, LPO, 9, 600

    1011, POL, 22, 700

];

Here simple data working fine for me, but my real data i have more than 10 common fields. when i am giving left join not working properly.  (But i want left join with one filed only)

My real data if i am using LEFT JOIN showing wrong result, but LEFT KEEP showing correct.

these are both same left join and left keep it should be correct result ???

Thanks in Advance

[squeakie_pig]

This is a great article outlining the difference between the different joins and keep

Understanding Join, Keep and Concatenate

Like Massimo said, maybe KEEP is the function you want instead of JOIN

[Anonymous]

Hi Paul,

Use this code:

QUALIFY *;
UNQUALIFY CatID;
TableA:
LOAD * INLINE [
    CatID, costcenter, amount
    1001, ABC, 300
    1001, BCA, 120
    1001, MKL, 900
    1000, jkl, 90
    1000, oip, 980
    1011, LPO, 650
    1011, POL, 320
];

left join
TableB:
LOAD * INLINE [
    CatID, costcenter, Chours, Wamount
    1001, ABC, 10, 100
    1001, ACB, 2, 200
    1111, KLJ, 7, 300
    1000, jkl, 12, 400
    1001, oip, 17, 500
    1111, LPO, 9, 600
    1011, POL, 22, 700
];

UNQUALIFY *;

This is the output: