Lastworkdate failing on script load

[thomasmercer]

Hi, I'm trying to add another column to a date set which is loaded in Data Manager. I can't figure out why I am receiving an error when trying to do calculated the date due of transactions using LastWorkDate. Here is the additional code:

Holidays2:
Load
Concat(chr(39) & HolidayDate & chr(39), ',') as ALL_HOLIDAYS Resident Holidays;
Let vHolidays = Peek('ALL_HOLIDAYS');
Drop table Holidays2;
Drop table Holidays;

JOIN ([ScoutSummary])
LOAD
date(Max([date_incident_reported]), 'MM/DD/YYYY') as MaxScoutDate,
lastworkdate([date_incident_reported], 3 , $(vHolidays)) as date_assessed_due
Resident ScoutSummary;

Below is the error:

The following error occurred: Invalid expression The error occurred here: JOIN ([ScoutSummary]) LOAD date(Max([date_incident_reported]), 'MM/DD/YYYY') as MaxScoutDate, lastworkdate([date_incident_reported], 3 , '01/01/2018','01/01/2019','01/01/2020','01/04/2018','02/04/2018','03/11/2020','05/11/2019','06/11/2018','08/06/2020','09/03/2020','10/04/2020','10/06/2019','11/03/2019','11/04/2020','11/06/2018','12/03/2018','12/04/2020','13/04/2020','19/04/2019','20/04/2019','21/04/2019','22/04/2019','25/04/2018','25/04/2019','25/04/2020','25/12/2018','25/12/2019','25/12/2020','26/01/2018','26/12/2018','26/12/2019','27/01/2020','27/09/2019','28/01/2019','28/09/2018','28/12/2020','30/03/2018','31/03/2018') as date_assessed_due Resident ScoutSummary

If I comment out the LastWorkDate line everything loads okay, so I know the error is specific to this:

lastworkdate([date_incident_reported], 3 , $(vHolidays))

If I use the above line in a master dimension, it also calculates correctly.

Could someone help figure out why this errors in the data load editor?

Thanks

Thomas

[tresesco · Accepted solution]

It is perhaps error due to group by issue. If you need this to work on max date, try like:

LOAD
            date(Max([date_incident_reported]), 'MM/DD/YYYY') as MaxScoutDate,
            lastworkdate(Max([date_incident_reported]), 3 , $(vHolidays)) as date_assessed_due
Resident ScoutSummary;

[thomasmercer]

Thanks @tresesco , can confirm having the first criteria with Max was causing the group by. If I separate out it works without issue. Cheers