topic Re: join in QlikView

join

Fri, 09 May 2014 06:24:10 GMT

hello all,

i need to join the below tables such that transdate of 1st table matches transdate of 2nd,MID of 1st table matches 2nd,tid of 1st table matches 2nd uploaddate of 1st table matches with 2nd.

Re: join

amit_saini — Fri, 09 May 2014 06:32:27 GMT

Hi Rohit ,

As both the tables having similar fields mentioned by you Like TRANDATE , MID , so it will automatic join these two tables by the common names.

Thanks,

Re: join

Fri, 09 May 2014 06:33:57 GMT

Do u want to join the 2 tables based on all the three fields?

Re: join

Fri, 09 May 2014 06:41:26 GMT

hello priya ,

yes ,i want to join it based on all three fields , i have sql condition like a.TRANDATE=b.TRANDATE and a.MID=b.MID and a.TID=b.TID

Re: join

Fri, 09 May 2014 06:41:56 GMT

i have sql condition like a.TRANDATE=b.TRANDATE and a.MID=b.MID and a.TID=b.TID, how can i implement the same in qlikview

Re: join

Fri, 09 May 2014 06:42:10 GMT

i have sql condition like a.TRANDATE=b.TRANDATE and a.MID=b.MID and a.TID=b.TID, how can i implement the same in qlikview

Re: join

Fri, 09 May 2014 06:47:48 GMT

Since your fieldnames of the key-fields have the same name, Qlikview will make the right relation for the join.

The syntax will be

CCfirstcutmaster:

load

* from table1;

left join(CCfirstcutmaster:)

load * FROm table2;

obviously you need to change the code so that the tablenames etc. are right, and you need to make sure that in instead of load *, the fieldnames you mention are mentioned, and that only key-fields have the same name in both tables.

Re: join

Fri, 09 May 2014 06:51:27 GMT

Then u can create the concatenated keys of all 3 fields and comment those fields from the table otherwise it will create the synthetic keys.

CCfirstcutmaster:

Load

TranDate &'-' & MID &'-' & TID &'-' & UPLOADDATE as Key,

Remaining fieldnames

from CCfirstcutmaster.qvd;

Load

TranDate &'-' & MID &'-' & TID &'-' & UPLOADDATE as Key,

Remaining fieldnames

from Transaction.qvd;

In this case it will join 2 tables based on Key.

Hope this will help u..!

Re: join

anbu1984 — Fri, 09 May 2014 06:56:09 GMT

By default Qlikview perform outer join. QlikView joins are based on that the two key fields are named the same.

In your tables, you have ReferenceNo field which is present in both the tables. If you dont want to join through this column then rename it as below in one of the tables.

If you want to perform inner join or left or right, then you have to mention type of join as below

Table1:

Load ReferenceNo as RefNo,

Join(Table1)

Table2:

Load ...

Re: join

its_anandrjs — Fri, 09 May 2014 07:05:09 GMT

I believe there is no need of creating the key field because you have many same fields in the tables then it will join with previous table see the script.

1. First way of doing this on the basis of the same fields table joins just put Join key word between the tables

CCtransaction:

LOAD

Referenceno,//Same Field

TRANDATE,//Same Field

CRCDNUM,

SERPROV,//Same Field

AUTHCD,

GRSAMT,

MID,//Same Field

TID,//Same Field

UPLOADDATE,//Same Field

FINALSTATUS//Same Field

FROM

[..\Data\Qvd\CCtransaction.qvd](qvd)

Where FINALSTATUS <> 'Close';

Join

Ccfirstcutmaster:

LOAD

Referenceno,//Same Field

TRANDATE,//Same Field

CHQTYPE,

PAYTYPE,

SerProv,//Same Field

CHQNUM,

ORIGAMT,

MID,//Same Field

TID,//Same Field

UPLOADDATE,//Same Field

FINALSTATUS//Same Field

FROM

[..\Data\Qvd\CCfirstcutmaster.qvd](qvd);

2. Second way for doing this is based on the Autonumber key field but not necesary to make key like this.

CCtransaction:

LOAD

AutoNumberHash128(TRANDATE,MID,TID,UPLOADDATE) as %Key,

Referenceno,//Rename The Same Fields and use other fields also

FROM

[..\Data\Qvd\CCtransaction.qvd](qvd)

Where FINALSTATUS <> 'Close';

Join

Ccfirstcutmaster:

LOAD

AutoNumberHash128(TRANDATE,MID,TID,UPLOADDATE) as %Key,

Referenceno,//Rename The Same Fields and use other fields also

FROM

[..\Data\Qvd\CCfirstcutmaster.qvd](qvd);

Note:- First approach is better and after load check the duplicate or the uniqueness of the records

Re: join

SunilChauhan — Fri, 09 May 2014 07:11:41 GMT

i thing you should simply use

Inner join between them

Load

from firsttable

inner join

load from seond table

hope this helps

Re: join

SunilChauhan — Fri, 09 May 2014 07:21:11 GMT

on emor thing you can do

Tab1:

Load *,

Transdate&Mid&Tid&Uploaddate as key

from table1

Tab2;

Load

from

where FlnalStatus<>'Close' and exist(key,Transdate&Mid&Tid&Uploaddate);

Drop table Tab1;

try implementing logic on this

hope this helps

Re: join

Fri, 09 May 2014 07:21:36 GMT

Hi,

If you want join this tables by using three common fields means. Should create composite Key. Otherwise qlikview will create the synthetic keys.

CCfirstcutmaster:

Load

TranDate &'-' & MID &'-' & TID &'-' & UPLOADDATE as CompKey,

Remaining fieldnames

from CCfirstcutmaster.qvd;

Load

TranDate &'-' & MID &'-' & TID &'-' & UPLOADDATE as CompKey,

Remaining fieldnames

from Transaction.qvd;

in this scenario this two table will join based on CompKey

Hope this will help you...

Re: join

Fri, 09 May 2014 07:31:45 GMT

Rajasekhar Reddy Mk: I disagree with your previous reply. You can without problems, join 2 tabes based on 3 key-fields without making a composite key. Note, it is not wrong to make a composite key, but also it is not required: when there are 3 key fields,Qlikview will join based on these 3 fields. And because you performe a join, there will not be a synthetic key or table because the join combines these 2 tables into 1.