topic Re: Join help in QlikView

Join help

paulwalker — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Join help

paulwalker — Wed, 19 Oct 2016 18:05:51 GMT

My doubt...

how it works left join ??

if i have more than one common field.

Please anyone can explain me

Re: Join help

krishnacbe — Wed, 19 Oct 2016 18:17:17 GMT

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.

Re: Join help

krishnacbe — Wed, 19 Oct 2016 18:18:14 GMT

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

Re: Join help

krishnacbe — Wed, 19 Oct 2016 18:20:59 GMT

Hope the attached qvw will help.

Re: Join help

Anonymous — Wed, 19 Oct 2016 18:25:13 GMT

It is not better to union?

Re: Join help

maxgro — Wed, 19 Oct 2016 18:25:59 GMT

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‌

Re: Join help

martinpohl — Wed, 19 Oct 2016 19:09:48 GMT

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

Re: Join help

MarcoWedel — Wed, 19 Oct 2016 23:15:04 GMT

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

Re: Join help

Anil_Babu_Samineni — Thu, 20 Oct 2016 02:12:40 GMT

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

Re: Join help

squeakie_pig — Thu, 20 Oct 2016 13:35:16 GMT

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

Re: Join help

Anonymous — Thu, 20 Oct 2016 13:49:40 GMT

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: