Joining two Excel files using odbc

[#1]

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?

[jonathandienst]

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

[jonathandienst]

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);

[Ralf-Narfeldt]

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.

[#5]

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.