group by must have all fields in the select, why?

[#1]

hi everyone,

i have

SR_NO	SR_STATUS	SR_STATUS_DATE
123	Open	01 jan
123	Closed	15 jan

i want to know what's the latest status for each SR, one SR being identified by its number (SR_NO)

i'm trying to load a temporary table as :

SR_TMP:

load SR_NO,

     SR_STATUS,

     SR_STATUS_DATE,

     max(SR_STATUS_DATE) as MAX_SR_STATUS_DATE

from [$(vQVDBasePath)\SERVICE_REQUESTS_STATUSES.qvd] (qvd)

group by SR_NO;

so that in a subsequent step i can do:

load SR_NO, SR_STATUS

resident SR_TMP

where SR_STATUS_DATE = MAX_SR_STATUS_DATE;

but this errors and i found that that's because all the fields in the select clause must be in the group by clause, but if i do this, it won't give me the most recent status for each SR.

how can i do this?

thanks for your help

Alex

[maxgro · Accepted solution]

I used an inline load because I don't have your qvd.

Also I added 2 rows (1123) and change the status date (in your data the year is missing)

source:

load SR_NO, SR_STATUS, Date(Date#(SR_STATUS_DATE, 'DD-MMM-YYYY')) as SR_STATUS_DATE inline [

SR_NO, SR_STATUS, SR_STATUS_DATE

123, Open, 01-jan-2015

123, Closed, 15-jan-2015

1123, Open, 01-jan-2015

1123, Closed, 15-jan-2015

];

inner join (source)

load

  SR_NO,

  Date(max(SR_STATUS_DATE)) as SR_STATUS_DATE

Resident source

group by SR_NO; 

Result is

[jldengra]

A single step aggregation cannot provide the maximum value of SR_STATUS_DATE grouping by SR_NO and the disaggregated values of the fields SR_STATUS and ST_STATUS_DATE. I would do it in two steps, one for getting the raw data and other to join the aggregated maximum value.

SR_TMP:

LOAD SR_NO,

     SR_STATUS,

     SR_STATUS_DATE   

FROM [$(vQVDBasePath)\SERVICE_REQUESTS_STATUSES.qvd] (qvd);

INNER JOIN (SR_TMP)

LOAD SR_NO, max(SR_STATUS_DATE) as MAX_SR_STATUS_DATE

RESIDENT SR_TMP

GROUP BY SR_NO;

[MarcoWedel]

FirstSortedValue(SR_Status, -SR_Status_Date)

Regards

Marco

[jldengra]

FirstSortedValue would be convenient only if it is intended to be calculated for a given expression and depending on the current selection, not for loading this maximum value as a field in the data model.

[Peter_Cammaert]

Why not?

LastStatus:

LOAD SR_NO,

     Max(SR_STATUS_DATE) AS MAX_DATE,

     FirstSortedValue(SR_STATUS, -SR_STATUS_DATE) AS MAX_DATE_STATUS

FROM [$(vQVDBasePath)\SERVICE_REQUESTS_STATUSES.qvd] (qvd)

GROUP BY SR_NO;

Peter

[jldengra]

Because when trying to make use of FirstSortedValue function in the load, a script Error occurs because it is an "Invalid expression" for the script.

[Peter_Cammaert]

Not in my QlikView. FirstsortedValue is a regular aggregation function to be used in LOAD statements with a GROUP BY clause (obligatory). See QV Help.

Maybe you made another syntax error?

[jldengra]

Thank you, Peter. You are right. I was testing the function without a GROUP BY clause, and it was my mistake.

Thus, FirstSortedValue is also an aggregation function suitable to be used in loads.

Anyway I prefer to compute it dynamically inside a chart expression.