Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
My doubt...
how it works left join ??
if i have more than one common field.
Please anyone can explain me
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.
In above example you can left join using CATID and rename the costcenter field.
Hope the attached qvw will help.
It is not better to union?
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
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
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
Can I have your expected table with values because this is bit tricky. So, we might help you on function with one speCific field