Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create a new table that contains the average length of stay by state based on joining the data from the 2 qvd files.
I am doing something wrong and can't figure it out.
SNF_Info:
LOAD MEDPAR_PROVIDER_NUMBER,
"Provider State"
FROM [lib://QVD Files/MedPar\Medpar_Nursing_Homes.QVD](qvd);
Join(SNF_Info)
LOAD
"LENGTH_OF_STAY",
ServiceLineDescription,
ServiceLineNum,
"MEDPAR_PROVIDER_NUMBER"
FROM [lib://QVD Files/MedPar\Medpar_SNF_Data_2014.QVD](qvd);
BaseLine:
Load
"Provider State",ServiceLineDescription,
AVG([LENGTH_OF_STAY]) as ALOS
Resident SNF_Info Group By "Provider State";
Drop Table SNF_Info;
Figured it out. You can only have the fields loaded that are either in the Group By or the Aggr,
I had State in the Load but not anywhere else.
You don't calculate average in script,
It should be done in chart as sum (lenghtofstay) / count (servicelinenumber)
Still getting an invalid expression error in the BaseLine section
Didn't notice you were using AVG in script it's a chart function
But your expression should actually be as below
sum (lenghtofstay) / count (servicelinenumber)
That still is not working. Expression error.
Figured it out. You can only have the fields loaded that are either in the Group By or the Aggr,
I had State in the Load but not anywhere else.
Bonjour,
SNF_Info:
LOAD
"LENGTH_OF_STAY",
ServiceLineDescription,
ServiceLineNum,
"MEDPAR_PROVIDER_NUMBER"
FROM [lib://QVD Files/MedPar\Medpar_SNF_Data_2014.QVD](qvd);
left join
LOAD MEDPAR_PROVIDER_NUMBER,
"Provider State"
FROM [lib://QVD Files/MedPar\Medpar_Nursing_Homes.QVD](qvd);
TMP:
Load
"Provider State",
AVG([LENGTH_OF_STAY]) as ALOS
Resident SNF_Info Group By "Provider State";
BaseLine:
left join(SNF_Info)
load *
resident TMP
;
drop table SNF_Info;
... may be ?
Hi ,
Have you read this post?
Avg() finds the average value of the aggregated data in the expression over a number of records as defined by a group byclause.
The reason I mentioned not to do this in script is because your average would then be STATIC.
In your script your average will be based on ProvideState,ServiceLine ,
To See how they remain static do the below
Create 2 Straight Tables,
Table1:
Dimension: Provider State , ServiceLine
Expression: ALOS
Table2:
Dimension: Provider State , ServiceLine
Expression: sum ([Length of Stay]) / count (ServiceLine)
Now remove dimension ServiceLine from both the tables and see what happens.
Table 2 will dynamically evaluate Average by ProviderState, but Table 1 will continue to show you static values.
IT IS COMPLETELY UP TO YOU TO SHOW STATIC VALUES!