Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi,
I think you can try with extended IntervalMatch function, see attachment
This is what I need. Could you please drop a few lines telling me how it works. Thanks
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.