I have a problem where I need find if the max date is I have for a trade was on Sunday and if so the deduct 2 days worth of fees. i have tried the below code on load but keep invalid expression. Is this something that can be done?
Weekday() returns an integer between 1-6. In your case, simply replace 'Sun' with '6'. If that doesn't work either, make sure MyDate has a correct Date format and is not a string.
You cannot use the Max() function in a LOAD statement that has no GROUP BY clause (only one exception: use only aggregation functions to create single record with statistics from a full table - see Master Calendar examples)
One solution is to group your records by a "Trade" identifier and then extract the max date. Join the max date back into the table to decide where to subtract 2 days worth of fees.
Your loading all the fields in load statement (Load *). In this case you need all the fields in your groupby statement. So try without *, load as separate table
GROUP BY aggregates records, i.e. reduces the number of records to a series of summaries.
Only fields specified in the GROUP BY clause can be listed in the LOAD expressions as-is or in expressions without aggregation functions.
All fields specified in the GROUP BY clause should be used somewhere in the LOAD list.
All other fields not specified in the GROUP BY clause can only be listed as LOAD fields if they are used inside aggregation functions (i.e. Max, Min, Count, Sum etc.)
Perform a two step approach:
First for every "Trade", extract the max date (use a GROUP BY clause to load records in a new table). Then JOIN the max date back into the original table based on "Trade" identifier (or even better, create a mapping table and use applymap() to translate every "Trade" ID into a max date value)
Now step through your original table, and decide on where to subtract 2 days worth of fees based on the max date field included in every record. This one can be done sequentially, without GROUP BY.
