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 Please

I am attempting to load the max value of a field and put it into a variable.

For some reason I am not getting any value loaded.  Here is the script that I am running.  I am clearly doing something wrong.

Outcomes:

LOAD

    CUSTOMERID

Resident Customers;

Left Join

LOAD

    CustomerID & DischargeYear & '-' & DischargeMonth as Key,

    //Division,

    //Region,

    //District,

    //Chain,

    Facility,

    CaseNumber,

    Payor,

    //PatientName,

    MeasureDesc,

    SRCategory,

    Discipline, 

    MeasureGroup_Scale,

    MeasureValueStart,

    MeasureValueEnd,

    //Gain,

    //PreMorbid,

    //Goal,

    //Interim,

   // MetGoal,

    //BackPreMorbid,

    LOS,

    CustomerID as CUSTOMERID,

    GroupDesc,

    Category,

    ValueDesc,

    EOCDest,

    EOCDestFilter,

    PatientID,

    DischargeDate,

    SecondaryProgramDesc,

    DischargeMonth,

    DischargeYear,

    Discharge_Year_Month,

    Load_Date,

    VECTR,

    IN_PAT,

    ADM_TYP,

    ADM_DT,

    ADM_MNTH,

    ADM_YR,

    ADM_YR_MNTH,

    DSCHRG_YR_MNTH,

    PRGM_CNSUS_DSCHRG

   

FROM [lib://QVD Files/Healthmax/Outcomes.QVD] (qvd);

tmp:

Load Max(Discharge_Year_Month) As max_Discharge_Period

Resident Outcomes;

Let vMaxDate=Peek('max_Discharge_Period');

//Drop Table tmp;

1 Solution

Accepted Solutions
sunny_talwar

May be like this:

Let vMaxDate = Date(Peek('max_Discharge_Period'), 'YYYY-MM');

But why is number not  good? You can convert a number to a date anytime you want, converting a text Year-Month to a dual Year_Month field requires some additional steps.

View solution in original post

7 Replies
sunny_talwar

Since you are doing a Left Join from your second table, does any of the CustomerID match between the two tables? Because is no CustomerID match, the table that you are joining from will not bring anything.

rittermd
Master
Master
Author

I just added the tmp table portion.  The rest of the script works fine and returns the expected data.

sunny_talwar

Then you need to make sure that Discharge_Year_Month is read as a dual field within QlikView. Read about dates in QlikView here:

Why don’t my dates work?

Get the Dates Right

rittermd
Master
Master
Author

It is actually not a date.  It is a field that combines just the year and month.  Like 2016-9.

sunny_talwar

May be try like this:

Outcomes:

LOAD

    CUSTOMERID

Resident Customers;

Left Join

LOAD

    CustomerID & DischargeYear & '-' & DischargeMonth as Key,

    //Division,

    //Region,

    //District,

    //Chain,

    Facility,

    CaseNumber,

    Payor,

    //PatientName,

    MeasureDesc,

    SRCategory,

    Discipline,

    MeasureGroup_Scale,

    MeasureValueStart,

    MeasureValueEnd,

    //Gain,

    //PreMorbid,

    //Goal,

    //Interim,

   // MetGoal,

    //BackPreMorbid,

    LOS,

    CustomerID as CUSTOMERID,

    GroupDesc,

    Category,

    ValueDesc,

    EOCDest,

    EOCDestFilter,

    PatientID,

    DischargeDate,

    SecondaryProgramDesc,

    DischargeMonth,

    DischargeYear,

   Date(Date#(Discharge_Year_Month, 'YYYY-M'), 'YYYY-M') as Discharge_Year_Month,

    Load_Date,

    VECTR,

    IN_PAT,

    ADM_TYP,

    ADM_DT,

    ADM_MNTH,

    ADM_YR,

    ADM_YR_MNTH,

    DSCHRG_YR_MNTH,

    PRGM_CNSUS_DSCHRG

  

FROM [lib://QVD Files/Healthmax/Outcomes.QVD] (qvd);

tmp:

Load Max(Discharge_Year_Month) As max_Discharge_Period

Resident Outcomes;

Let vMaxDate=Peek('max_Discharge_Period');

//Drop Table tmp;

rittermd
Master
Master
Author

That gave me a result.  So progress.

But the value it gave me in the variable was 42,705

That doesn't help me.  I need to know the latest period (YYYY-MM) in the data to use in an expression.

sunny_talwar

May be like this:

Let vMaxDate = Date(Peek('max_Discharge_Period'), 'YYYY-MM');

But why is number not  good? You can convert a number to a date anytime you want, converting a text Year-Month to a dual Year_Month field requires some additional steps.