topic Re: Qlikview default join (Associate) Vs Join between tables in QlikView

Qlikview default join (Associate) Vs Join between tables

narender123 — Mon, 30 Nov 2015 11:39:32 GMT

Hi All,

I have 2 tables.I want to merge it into one via join.

Case1:-

table a:

ref,

amount,

----

from table a;

table b:

ref,

Tax,

----

from table b;

Qlikview by default creating the join via ref and showing right data.

--------------------------------------------------------------------------------------------------------

Now i want to merge both table into 1 via join ref.

Case2:-

table a:

ref,

amount,

----

from table a;

join

table b:

ref,

Tax,

----

from table b;

But it is showing wrong data.What should i do to show right result during merging into 1 table.

table b having multiple entries of ref.But table 1 having single entry of ref.

Please suggest me to sort out this.

Thanks,

Re: Qlikview default join (Associate) Vs Join between tables

Anonymous — Mon, 30 Nov 2015 12:32:16 GMT

I tried the described approach with my sample data and it gives me same result both using association and join.

Can you share your sample file if possible, which helps me to investigate further?

Attached the file where I tried the scenario.

Re: Qlikview default join (Associate) Vs Join between tables

Anonymous — Mon, 30 Nov 2015 12:40:48 GMT

see this for better understanding:

Understanding Join, Keep and Concatenate

Re: Qlikview default join (Associate) Vs Join between tables

narender123 — Mon, 30 Nov 2015 12:45:42 GMT

Hi Leni,

I cant show.

Thanks,

Re: Qlikview default join (Associate) Vs Join between tables

narender123 — Mon, 30 Nov 2015 12:47:16 GMT

Can you tell me which one is the exact join for the qlikview default (automatic) join?

As i already used these joins but no success.

Thanks,

Re: Qlikview default join (Associate) Vs Join between tables

maxgro — Mon, 30 Nov 2015 12:48:30 GMT

don't join

tablea:

load * inline [

ref, amount

1, 1

2, 2

3, 3

];

join

// tableb

load * inline [

ref, tax

1,1

1,11

2,2

2,22

3,33

];

I think you're replicating the amount when you have the same ref in tableb and the sum is wrong

Re: Qlikview default join (Associate) Vs Join between tables

Anonymous — Mon, 30 Nov 2015 12:50:47 GMT

By default QV takes Full Outer Join which may mismatch your data.

Also, while joining two table check their Information Denisty and & Subset Ratio

information density and subset ratio? What is the use of this?

Table with more Information Denisty and & Subset Ratio will be like a parent table for you

Re: Qlikview default join (Associate) Vs Join between tables

narender123 — Tue, 01 Dec 2015 10:56:24 GMT

Hi Maxgro,

I need to join them and make them one table.

and my expression is like eg:-

= sum(amount)+sum(tax)

Re: Qlikview default join (Associate) Vs Join between tables

narender123 — Tue, 01 Dec 2015 10:59:27 GMT

Hi Balraj,

You are right my data is taking full outer join records when i joined them. but i need records just like in qlikview default (automatic) join.Because my target is to merge into one table via joining .

Thanks,

Re: Qlikview default join (Associate) Vs Join between tables

Anonymous — Tue, 01 Dec 2015 11:00:32 GMT

Instead of join, you can use Mapping Load as well....with applymap()..

see this:

Don't join - use Applymap instead

Re: Qlikview default join (Associate) Vs Join between tables

jonathandienst — Tue, 01 Dec 2015 11:00:51 GMT

The join will not work if the join key has duplicate values, as this will result in the creation of extra row and either the tax or the amount (or both) will be duplicated.

Why do you need to make one table? The model calculations will work just as well if the tables are loaded as is.

Re: Qlikview default join (Associate) Vs Join between tables

Anonymous — Tue, 01 Dec 2015 11:04:09 GMT

say you are having two following tables like:

tablea:

mapping

load

ref,

amount

from tablea;

tableb:

load

ref,

applymap('tablea',ref,null()) as amount,

tax

from tableb;

This will give you one final table.

Note: Mapping load works only when you are fetching single field from other table.

Re: Qlikview default join (Associate) Vs Join between tables

maxgro — Tue, 01 Dec 2015 11:27:02 GMT

I still suggest don't join but, if you want to join, group the tax table

tablea:

load * inline [

ref, amount

1, 1

2, 2

3, 3

];

tableb:

load * inline [

ref, tax

1,1

1,11

2,2

2,22

3,33

];

join (tablea)

load ref, sum(tax) as tax

Resident tableb

group by ref;

DROP Table tableb;

Re: Qlikview default join (Associate) Vs Join between tables

himanshi — Tue, 01 Dec 2015 11:33:21 GMT

hiii,

you can use concatenate or resident load can also be used.

regards.