Multiple Resident Loads

[byrnel0586]

Hi,

This will sound complicated, but I am wanting to get some calculations done in the back end and am trying to create a table for them. I need to use fields from a table that has already been created in order to do the calculations. I am trying to do multiple loads using resident, but also need a where clause - when I attempt the below script I get the WeightedCal_74, 84, and 105 fields in the table and nothing else. However, when I remove the where clause I get all fields (as I would like).

I will also ultimately like to concatenate this table to include multiple month flags. Is there any way to do this??

PCR_Calc:

LOAD
WeightOfAdjProb65_74 * AvgAdjProb_65to74 as WeightedCal_74,
WeightOfAdjProb75_84 * AvgAdjProb_75to84 as WeightedCal_84,
WeightOfAdjProb85_105 * AvgAdjProb_85to105 as WeightedCal_105;

LOAD

Sum(If(AgeFlag=1,NUMERATOR_PCR,0)) as Age64_105Num,
Sum(AgeFlag74)/Sum(AgeFlag) as WeightOfAdjProb65_74,
Sum(AgeFlag84)/Sum(AgeFlag) as WeightOfAdjProb75_84,
Sum(AgeFlag105)/Sum(AgeFlag) as WeightOfAdjProb85_105,
Sum(If(AgeFlag74=1,[Average Probability of readmission],0))/Sum(AgeFlag74) as AvgAdjProb_65to74,
Sum(If(AgeFlag84=1,[Average Probability of readmission],0))/Sum(AgeFlag84) as AvgAdjProb_75to84,
Sum(If(AgeFlag105=1,[Average Probability of readmission],0))/Sum(AgeFlag105) as AvgAdjProb_85to105

Resident PCR_DETAILS_HEDIS2016

Where (Match (_PCR2_CalcKEY, 'PCR2Jan2017'));

[Clever_Anjos · Accepted solution]

LOAD *,
WeightOfAdjProb65_74 * AvgAdjProb_65to74 as WeightedCal_74,
WeightOfAdjProb75_84 * AvgAdjProb_75to84 as WeightedCal_84,
WeightOfAdjProb85_105 * AvgAdjProb_85to105 as WeightedCal_105;

LOAD

Sum(If(AgeFlag=1,NUMERATOR_PCR,0)) as Age64_105Num,
Sum(AgeFlag74)/Sum(AgeFlag) as WeightOfAdjProb65_74,
Sum(AgeFlag84)/Sum(AgeFlag) as WeightOfAdjProb75_84,
Sum(AgeFlag105)/Sum(AgeFlag) as WeightOfAdjProb85_105,
Sum(If(AgeFlag74=1,[Average Probability of readmission],0))/Sum(AgeFlag74) as AvgAdjProb_65to74,
Sum(If(AgeFlag84=1,[Average Probability of readmission],0))/Sum(AgeFlag84) as AvgAdjProb_75to84,
Sum(If(AgeFlag105=1,[Average Probability of readmission],0))/Sum(AgeFlag105) as AvgAdjProb_85to105

Resident PCR_DETAILS_HEDIS2016

Where (Match (_PCR2_CalcKEY, 'PCR2Jan2017'));

[sunny_talwar]

Its strange that without your where clause all the fields were loaded because I would have thought that in your current state, with or without the where statement you would only get 3 field. The reason being you missed * in your preceding load to load all the fields in the subsequent load.

[byrnel0586]

Thank you both! Silly mistake. So, I should be able to concatenate these statements as tables for each month going forward?