Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to join two data set from different databases?

Hi all,

     Merry Christimas:

     I need merge two different datasets which come from different database - one is from SQL server 2008 R2 and another one from Oracle database, in QLIKVIEW. The SQL server data includes MedicalRecordNo, AdmissionTime, DischargeTime, Name. Gener and birthday and HospitalAccountID. The Oracle data includes MedicalRecordNo, FlushotDate and Dose. I would like to merge these two dataset in Qlikview by using MRN. There might be multiple admissions for a patient. I need make sure the FlushotDate is between admission date and discharge date. I want to show all the SQL server data and the merged flushot data. How can I do it? I used APPLYMAP function to get some records, but the results are not what I want since it includes all the Oracle records.

Thanks

Longmatch.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Something like this should work:

<CONNECT TO SQL SERVER>

SQLData:
LOAD
MedicalRecordNo,
AdmissionTime,
DischargeTime,
Name,
Gener,
birthday,
HospitalAccountID
FROM <sql server>;

<CONNECT TO ORACLE>

OracleData_tmp:
LOAD
MedicalRecordNo,
FlushotDate,
Dose
FROM <oracle>;


LEFT JOIN (OracleData_tmp) LOAD
MedicalRecordNo,
AdmissionTime,
DischargeTime,
RESIDENT SQLData;

OracleData:
LOAD
MedicalRecordNo,
FlushotDate,
Dose
RESIDENT OracleData_tmp
WHERE FlushotDate>=AdmissionTime and FlushotDate<=DischargeTime;

DROP TABLE OracleData_tmp;

Regards,

Michael

View solution in original post

4 Replies
Anonymous
Not applicable
Author

Something like this should work:

<CONNECT TO SQL SERVER>

SQLData:
LOAD
MedicalRecordNo,
AdmissionTime,
DischargeTime,
Name,
Gener,
birthday,
HospitalAccountID
FROM <sql server>;

<CONNECT TO ORACLE>

OracleData_tmp:
LOAD
MedicalRecordNo,
FlushotDate,
Dose
FROM <oracle>;


LEFT JOIN (OracleData_tmp) LOAD
MedicalRecordNo,
AdmissionTime,
DischargeTime,
RESIDENT SQLData;

OracleData:
LOAD
MedicalRecordNo,
FlushotDate,
Dose
RESIDENT OracleData_tmp
WHERE FlushotDate>=AdmissionTime and FlushotDate<=DischargeTime;

DROP TABLE OracleData_tmp;

Regards,

Michael

maxgro
MVP
MVP

Hi,

I think you can try with extended IntervalMatch function, see attachment

Not applicable
Author

This is what I need. Could you please drop a few lines telling me how it works.  Thanks

Anonymous
Not applicable
Author

1. Loading SQLData from SQL Server.

2. Loading temporary table OracleData_tmp from Oracle.

3. Joining Admission and Discharge time in SQLData to the OracleData_tmp.  As a result, we have this OracleData_tmp with all fields it has in the data source plus the Admission and Discharge times.  It is still has all Oracle records, but now we can use new fields as conditions in the next step.

4. Reading data from the resident table OracleData_tmp into the final OracleData with the condition.

5. Deleting temporary table.