Every day, a report is automatically pulled into a folder on my desktop from SAP. The report contains sales orders that we have yet to ship, thus, if a sales key # is present on one day's report, and then absent on that of the next day, it has been shipped. I am trying to assign ship dates to sales keys. I have successfully done so using the aggr function:
=if(aggr(max(file_date_num),Sales_Key)<>$(var_todaynum),Date(aggr(max(file_date_num),Sales_Key)),Null())
The problem is that I need to create a dimension with this date so that I can build charts with the date as the X axis. Therefore, I must calculate the ship date in the script, and I can't do so with the aggr function, because it doesn't work in the script. The date the report is run is embedded into the file name, like so: master_ship_extract_20160125.txt. Therefore, I can pull the last few characters to create a dimension called file_date, with the following formula:
date(date#(mid(filename(),21,8),'YYYYMMDD')) as file_date
I need to return the most recent file date based on the sales key. I want the sales key to show up only once in the final output, with its appropriate ship date, which is the latest date that the sales key showed up in a report file.
I have tried doing a secondary load into another table like so:
Table2:
NoConcatenate Load
[Sales Doc.],
Item,
Sales_Key,
max(file_date) as MaxFileDate
Resident Master_Ship
Group by Sales_Key;
Drop table Master_Ship;
But this does not work. I am not sure what to do. Thank you for any help you may be able to provide.
Thank you!
