topic Re: Joining two Excel files using odbc in QlikView

Joining two Excel files using odbc

Fri, 17 Apr 2015 05:54:25 GMT

Hi Guys newbie here:

My question is in my edit script, I'm trying to join two excel sheets that are being pulled in via odbc. I'm just trying to join on bp.PATIENT_ID = aic.PATIENT_ID_a.

Here's the code:

bp:
ODBC CONNECT TO [BP_Grady;DBQ=C:\USERS\KREGIST\Documents\BP.xls];
sql SELECT `PATIENT_ID`,
     `VISIT_DT_ACTUAL`,
     `VISIT_DT`,
     `STRUCTURED_RESULT_TYPE_CD`,
     `LAB_TVAL`,
     AGE,
     `VALUE_LIMIT`
FROM `C:\USERS\KREGIST\Documents\BP.xls`.BP;

aic:
ODBC CONNECT TO [A1C_Grady;DBQ=C:\Users\kregist\Documents\A1C_97.xls];
SQL SELECT `PATIENT_ID_a`,
     `VISIT_DT_ACTUAL_a`,
     `VISIT_DT_a`,
     `STRUCTURED_RESULT_TYPE_CD_a`,
     `LAB_TVAL_a`,
     AGE_a,
     `VALUE_LIMIT_a`
FROM `C:\USERS\KREGIST\Documents\A1C_97.xls`.A1C;

Anyone have any thoughts?

Re: Joining two Excel files using odbc

jonathandienst — Fri, 17 Apr 2015 08:08:58 GMT

Is there any reason to be using ODBC for this load?

QV associates fields with the same name, so to associate on Patient_ID, for the second load, use

SQL SELECT `PATIENT_ID_a` As PATIENT_ID,

`VISIT_DT_ACTUAL_a`,

....

HTH

Jonathan

Re: Joining two Excel files using odbc

jonathandienst — Fri, 17 Apr 2015 08:17:22 GMT

Usually we would concatenate similar fact tables like this:

data:

LOAD PATIENT_ID,

VISIT_DT_ACTUAL,

VISIT_DT,

STRUCTURED_RESULT_TYPE_CD,

LAB_TVAL,

AGE,

VALUE_LIMIT,

'bp' As SOURCE

FROM

(biff, embedded lables, table is BP);

Concatenate(data)

LOAD PATIENT_ID_a As PATIENT_ID,

VISIT_DT_ACTUAL_a As VISIT_DT_ACTUAL,

VISIT_DT_a As VISIT_DT,

STRUCTURED_RESULT_TYPE_CD_a As STRUCTURED_RESULT_TYPE_CD,

LAB_TVAL_a As LAB_TVAL,

AGE_a As AGE,

VALUE_LIMIT_a As VALUE_LIMIT,

'aic' As SOURCE

FROM

(biff, embedded lables, table is A1C);

Re: Joining two Excel files using odbc

Ralf-Narfeldt — Fri, 17 Apr 2015 08:20:11 GMT

Also, they seem to have the same field structure, with all fields in the second table having a _a suffix. It could make sense to concatenate them to one table, and if needed add a field saying which source they came from.

bp:

LOAD *,

'bp' As SOURCE;

ODBC CONNECT TO [BP_Grady;DBQ=C:\USERS\KREGIST\Documents\BP.xls];

sql SELECT `PATIENT_ID`,

`VISIT_DT_ACTUAL`,

`VISIT_DT`,

`STRUCTURED_RESULT_TYPE_CD`,

`LAB_TVAL`,

AGE,

`VALUE_LIMIT`

FROM `C:\USERS\KREGIST\Documents\BP.xls`.BP;

aic:

LOAD

`PATIENT_ID_a` As `PATIENT_ID`,

`VISIT_DT_ACTUAL_a` As `VISIT_DT_ACTUAL`,

`VISIT_DT_a` As `VISIT_DT`,

`STRUCTURED_RESULT_TYPE_CD_a` As `STRUCTURED_RESULT_TYPE_CD`,

`LAB_TVAL_a` As `LAB_TVAL`,

AGE_a As Age,

`VALUE_LIMIT_a` As `VALUE_LIMIT`,

'A1C' As SOURCE;

ODBC CONNECT TO [A1C_Grady;DBQ=C:\Users\kregist\Documents\A1C_97.xls];

SQL SELECT `PATIENT_ID_a`,

`VISIT_DT_ACTUAL_a`,

`VISIT_DT_a`,

`STRUCTURED_RESULT_TYPE_CD_a`,

`LAB_TVAL_a`,

AGE_a,

`VALUE_LIMIT_a`

FROM `C:\USERS\KREGIST\Documents\A1C_97.xls`.A1C;

The data will be stored in a single table, with an added SOURCE field that says if it came from BP or A1C. The re-naming is done in preceding loads.

Re: Joining two Excel files using odbc

Fri, 17 Apr 2015 11:28:52 GMT

Thanks for the responses. I forgot to mention that I wanted to keep the two tables separated. After some reading (and head-banging), I decided to rename the patient_id_a to patient_id and do a RIGHT KEEP. This game me the result I needed.