Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Do u want to join the 2 tables based on all the three fields?
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
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
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
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.
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..!
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 ...
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