Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rittermd
Honored Contributor

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
Honored Contributor

Re: Script Help

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.

9 Replies
vinieme12
Esteemed Contributor II

Re: Script Help

You don't calculate average in script,

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

rittermd
Honored Contributor

Re: Script Help

Still getting an invalid expression error in the BaseLine section

vinieme12
Esteemed Contributor II

Re: Script Help

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)

rittermd
Honored Contributor

Re: Script Help

That still is not working.  Expression error.

rittermd
Honored Contributor

Re: Script Help

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
New Contributor III

Re: Script Help

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
Esteemed Contributor II

Re: Script Help

Hi ,

Have you read this post?

Average – Which average?

nicolas66
New Contributor III

Re: Script Help

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
Esteemed Contributor II

Re: Script Help

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!

Community Browser