Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

maternmi
Contributor II

Firstsortedvalue issue

Hello,

Every production order  (Prod_Order_Change_Reason) can have null or more then one change reasons, which will be shown as number. To each change reason I have a timestamp (Prod_Order_Change_Time). So I want to have the last change reason to each production order.

Here the abstract from my script:

PickUp_Reliability:

load

     Firstsortedvalue (Prod_Order_Change_Reason,-Prod_Order_Change_Time) as Prod_Order_LastChange_Reason,

     *

;

load

      ID_SOP,

      Prod_Order_Change_Reason,

      Prod_Order_Change_Time,

     Billing_Date,

     OC_Date

resident

tmpTab

;

By reloading the script I've got an error "invalid expression". I get it only when I add the load order Firstsortedvalue....

Does somebody knows what is wrong?

Thanks!

BR

Michael

1 Solution

Accepted Solutions
MVP
MVP

Re: Firstsortedvalue issue

FirstSortedValue() is a aggregation function and must be used with a group by statement in your script:

PickUp_Reliability:

load

      ID_SOP,

      Prod_Order_Change_Reason,

      Prod_Order_Change_Time,

     Billing_Date,

     OC_Date

resident

tmpTab


load

     ID_SOP,

     Firstsortedvalue (Prod_Order_Change_Reason,-Prod_Order_Change_Time) as Prod_Order_LastChange_Reason

RESIDENT PickUp_Reliability GROUP BY ID_SOP;

1 Reply
MVP
MVP

Re: Firstsortedvalue issue

FirstSortedValue() is a aggregation function and must be used with a group by statement in your script:

PickUp_Reliability:

load

      ID_SOP,

      Prod_Order_Change_Reason,

      Prod_Order_Change_Time,

     Billing_Date,

     OC_Date

resident

tmpTab


load

     ID_SOP,

     Firstsortedvalue (Prod_Order_Change_Reason,-Prod_Order_Change_Time) as Prod_Order_LastChange_Reason

RESIDENT PickUp_Reliability GROUP BY ID_SOP;