Data Modelling

[Ciara]

I'm looking for assistance on how to import only specific data from a qvd based on criteria from both the data currenly in my data load editor and information in the qvd I'm wanting data from.

I have a table with procedures (OpBillingCode) performed and if the patient was an inpatient or an outpatient.

I have a separate table that just gives me the charges associated based on different FeeScheduleID's.  Here is just an example of one code.

What I'm trying to do in my data load editor is:

If InpatientOrOutpatient  equals 'O' and there is a charge amount for VHI DC then give me the ChargeAmount for VHI DC otherwise give me the ChargeAmount for VHI FPP B/C.

If InpatientOrOutpatient equals 'I' and there is a charge amount for VHI FPP B/C then give me the ChargeAmount for VHI FPP B/C otherwise give me the ChargeAmount for VHI DC.

I dont want to concatenate both tables but I'm at a loss as to how to write the syntax needed for what I'm looking for.

Any help is much appreciated.

Thanks

Ciara

[Prashant_Naik]

Hi Ciara,

This might help you : 

table_1:

load 

AutoNumber(OpBillingCode&'-'&InpatientOrOutpatient) as JoinKey,

VisitID,InpatientOrOutpatient,OpBillingCode from qvd1.qvd;

//i am considering you have 2 values in your table_2.

Left join(table_1)

Load 

AutoNumber(OpBillingCode&'-'&if(Wildmatch(FeeScheduledID,'VHI F*'),'I','O')) as JoinKey,

OpBillingCode as table2OpBillingCode,FeeScheduledID,ChargeAmount from Qvd2.qvd where wildmatch(FeeScheduledID,'VHI F*','VHI D*');

by loading the above script you will bring chargeamount against your visitors.

Regards,

Prashant

[Ciara]

This community is just amazing!  Thank you SO much Prashant.  I'm not entirely sure I know what you did here but it worked 😁  I'll do some research on the AutoNumber function.

Thanks again

Ciara

[Prashant_Naik]

Welcome😁

[Ciara]

Hi Prashant

Wondering if you could assist again on this.  I've discovered a snag with this coding.  It appeared to be working well until I discovered that it doesn't take into account if there is no ChargeAmount for one of the FeeSchedules.

For example, I have an OpBillingCode - 455 that only has a FeeSchedule for VHI DC.

With the above suggestion, there is no charge coming across for any Inpatient that happens to have this code.

I was trying to figure out how I can write.... If ChargeAmount = 0, then use the ChargeAmount for the other FeeSchedule.