Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
Hi
Try like this
Group by owsID, owsPrice;
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?
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;
Excellent, this solved the problem. Thank you!
I took away the Group By for the outer join though.