Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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);
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.
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.