Mapping and joins

[#1]

I have re-written my script to incorporate mapping and joins to create one table before sending it to a QVD. When I write my first mapping have a four column xls file...key field and three data fields I want to send to the main table. When I run the script it gives me an error that the field I am sending is not found. Please look at the following and let me know what needs to be corrected.

Thanks

Glen

//********************Facility Load******************

Facility:

Mapping LOAD

FacilityName,

A_FTYPE

FROM

(biff, embedded labels, table is Sheet1$);

Mapping LOAD

FacilityName,

A_FAC_NAME

FROM

(biff, embedded labels, table is Sheet1$);

Mapping LOAD

FacilityName,

A_REG

FROM

(biff, embedded labels, table is Sheet1$);

ODBC CONNECT TO MeditechDREH;

Visits:

Load

AdmitDateTime,

DischargeDateTime as Disch_Date,

interval(num(DischargeDateTime)-num(AdmitDateTime)) as LOS,

PatientID as ACCT_NUM,

FacilityName & PrimaryInsuranceID as FAC_INS,

FacilityName,

PrimaryInsuranceID as InsuranceID,

FacilityName,

InpatientOrOutpatient,

Month(DischargeDateTime) as Month,

Year(DischargeDateTime) as Year,

VisitID as MT_VISIT,

applymap('Facility',A_FAC_NAME,0) as F_NAME,

applymap('Facility',A_FTYPE,0),

applymap('Facility',A_REG,0);

SQL SELECT *

FROM LiveEH.dbo.BarVisits

Where InpatientOrOutpatient='I';

//********************Insurance Load**********************

ODBC CONNECT TO MeditechDREH;

Insurance:

Mapping Load

Name as INS_NAME;

SQL SELECT *

FROM LiveEH.dbo.DMisInsurance;

//***************************Patient Financial Info*******************

Fin_Data:

Load

INS_Name;

Load

AdjustmentTotal,

Balance as BAL,

BarStatus,

ChargeTotal as CHARGES,

ReceiptTotal as RECEIPTS,

RefundTotal as REFUNDS,

VisitID as MT_VISIT

applymap('Insurance',INS_NAME,0)

SQL SELECT *

FROM LiveEH.dbo.BarVisitFinancialData

WHERE (Balance=0 and BarStatus='FB'and ChargeTotal<>0) or (BarStatus='BD' and ChargeTotal<>0);

Left Join(Visits)

Load

Resident Fin_Data;

[martin59]

Hi,

You can try this :

Facility:
LOAD
FacilityName,
A_FTYPE
FROM 
(biff, embedded labels, table is Sheet1$);
LOAD
FacilityName,
A_FAC_NAME as A_FTYPE
FROM 
(biff, embedded labels, table is Sheet1$);
LOAD
FacilityName,
A_REG as A_FTYPE
FROM 
(biff, embedded labels, table is Sheet1$);

MapFacility:
NOCONCATENATE MAPPING LOAD
*
RESIDENT Facility;
DROP TABLE Facility;

...
applymap('MapFacility',...) as Facility,
...

[#3]

Martin,

Thanks, but why in the 2nd and 3rd load are the aliases the same as 1st, "A_FTYPE"? Aren't they supposed to be different so I can applymap them separately?

Glen

[martin59]

If the three original tables have to be used in the same mapping, use my solution.

If it's three different mappings, you have to use your script, but you should rename each mapping table.