Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Live Chat, June 29th 10AM ET. Bring your Qik Sense Enterprise, Client Managed questions! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Ciara
Creator
Creator

Data Modelling

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.

Ciara_0-1598885389815.png

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.

Ciara_1-1598885483050.png

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

 

 

4 Replies
Prashant_Naik
Partner
Partner

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
Creator
Creator
Author

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
Partner
Partner

Welcome😁

Ciara
Creator
Creator
Author

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.