Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rittermd
Master
Master

Script Help

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;

1 Solution

Accepted Solutions
rittermd
Master
Master
Author

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.

View solution in original post

9 Replies
vinieme12
Champion III
Champion III

You don't calculate average in script,

It should be done in chart as sum (lenghtofstay) / count (servicelinenumber)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
rittermd
Master
Master
Author

Still getting an invalid expression error in the BaseLine section

vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
rittermd
Master
Master
Author

That still is not working.  Expression error.

rittermd
Master
Master
Author

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.

nicolas66
Contributor III
Contributor III

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 ?

vinieme12
Champion III
Champion III

Hi ,

Have you read this post?

Average – Which average?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
nicolas66
Contributor III
Contributor III

Why not ?

Avg - script function

Avg() finds the average value of the aggregated data in the expression over a number of records as defined by a group byclause.

vinieme12
Champion III
Champion III

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!

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.