Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Thanks @tresesco , can confirm having the first criteria with Max was causing the group by. If I separate out it works without issue. Cheers