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

[paulwalker]

My doubt...

how it works left join ??

if i have more than one common field.

Please anyone can explain me

[krishnacbe]

Hi,

You can left join one field and rename other 9 common fields. or you can use qualify statement to avoid the multiple fields joining.

[krishnacbe]

In above example you can left join using CATID and rename the costcenter field.

[krishnacbe]

Hope the attached qvw will help.

[Anonymous]

It is not better to union?

[maxgro]

left join: the result is

- all the records of the first table (left)

- the records of the second table where all the common fields (CatID, costcenter) have the same value of the first table

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

];

maybe these can help you

http://qlikviewapuntes.blogspot.it/2014/01/esquema-de-joins.html‌

http://qlikviewapuntes.blogspot.it/2014/01/esquema-keep.html‌

[martinpohl]

How does join work?

IT's like the field link in two tables. If you join two tables with one common field you join both tables by this common field.

If there are more common field (like in your example), all common field are compared in the join tables (in your example CatId and costcenter).

I won't like to join your tables only by field CatId. Because all values from table one are joined (and multiplicated) by second table.

See only CountId 1001:

Three data sets in table one

Three data sets in tyble two

-> so you will get 9 data sets in your final table. Each line value from table one is getting three datasets from second table.

At all: Be careful by joining tables: Compare both (or more) tables before and after join. Is the number of records in the final table the right one? Or are there too many records because the join fields are not exact enough.

Regards

[MarcoWedel]

Why loading the common fields in the joining table in the first place if you don't want to join on them?

What's your expected result here?

regards

Marco

[Anil_Babu_Samineni]

Can I have your expected table with values because this is bit tricky. So, we might help you on function with one speCific field