join

[#1]

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.

[amit_saini]

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,

AS

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

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.    

[#8]

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..!

[anbu1984]

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 ...

[its_anandrjs]

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