FirstSortedValue in script

[#1]

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?

[MayilVahanan]

Hi

Try like this

Group by owsID, owsPrice;

[#3]

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?

[CELAMBARASAN · Accepted solution]

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;

[#5]

Excellent, this solved the problem. Thank you!

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