Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
mov
Esteemed Contributor III

Re: How to join two data set from different databases?

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

4 Replies
mov
Esteemed Contributor III

Re: How to join two data set from different databases?

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

MVP
MVP

Re: How to join two data set from different databases?

Hi,

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

Not applicable

Re: How to join two data set from different databases?

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

mov
Esteemed Contributor III

Re: How to join two data set from different databases?

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.

Community Browser