topic Re: FirstSortedValue in script in QlikView

FirstSortedValue in script

Fri, 13 Dec 2013 09:39:22 GMT

I’ve got the below script, joining two Sharepoint lists (tasks and projects). The two are linked by the ProjectID. Each project may have several tasks attached to it.

Report:
LOAD
owsDate AS TaskGoLiveDate,
owsID AS TaskID
owsProject AS ProjectID
FROM (sharepoint task list)

Outer Join (Report)
LOAD
owsID AS ProjectID,
owsPrice AS ProjectPrice
FROM (sharepoint project list)

In a chart I’ve got the following expression to show each projects latest TaskGoLiveDate:

=FirstSortedValue(TaskGoLiveDate, TaskID)

However, I’d like to do this at script level instead. I tried doing with the following:

Report:
LOAD
owsDate AS TaskGoLiveDate,
owsID AS TaskID
owsProject AS ProjectID
FROM (sharepoint task list)

Outer Join (Report)
LOAD
owsID AS ProjectID,
owsPrice AS ProjectPrice,
FirstSortedValue(TaskGoLiveDate, TaskID) AS ProjectGoLiveDate
FROM (sharepoint project list) GROUP BY ProjectID

This gives me an undefined error when I try to load the script. Can anyone please point me in the right direction what I am doing wrong?

Re: FirstSortedValue in script

MayilVahanan — Fri, 13 Dec 2013 09:42:59 GMT

Try like this

Group by owsID, owsPrice;

Re: FirstSortedValue in script

Fri, 13 Dec 2013 09:53:55 GMT

Thank you but I still get the same error (execution failed). Could it be that since I'm joining tables that I have to group by also the fields in the first list I'm loading?

Re: FirstSortedValue in script

CELAMBARASAN — Fri, 13 Dec 2013 10:27:08 GMT

Hi,

You cannot use the fields which are in other tables. You need to combine first and then use it

Hope the below script helps.

Report:

LOAD

owsDate AS TaskGoLiveDate,

owsID AS TaskID

owsProject AS ProjectID

FROM (sharepoint task list)

Outer Join (Report)

LOAD

owsID AS ProjectID,

owsPrice AS ProjectPrice

FROM (sharepoint project list) GROUP BY ProjectID

Left Join(Report)

LOAD,

ProjectID,

FirstSortedValue(TaskGoLiveDate, TaskID) AS ProjectGoLiveDate

Resident Report

Group By ProjectID;

Re: FirstSortedValue in script

Fri, 13 Dec 2013 10:43:43 GMT

Excellent, this solved the problem. Thank you!

I took away the Group By for the outer join though.