TM1 Forum

Hello again guys, I'm facing difficulties in getting the last possible date in my .CSV file. Here's the scenario:

Supposed I have a CSV file with 'Date' column (together with other columns). Example, I have 66 rows of 5/1/2017 (May 1, 2017) data, 10 rows of 5/2/2017 (May 2, 2017) data, and so on and so forth 'til 5/31/2017 (May 31, 2017). Now, this file is being processed every first week of the following month (so for this, it would June). Because this is End-of-Month file processing, output should get only the last/latest date available in the CSV file (in this case, 5/31/2017). Regardless if it 5/1/2017, 5/2/2017, 5/3/2017, etc... it should be replaced by 5/31/2017 to still include them in the output.

Also, I would want to change the format of the processed 'Date' into dd/mm/yyyy (31/05/2017).

Thanks in advance gurus!
Michael

macklovesraine11 wrote:Hello again guys, I'm facing difficulties in getting the last possible date in my .CSV file. Here's the scenario:

Supposed I have a CSV file with 'Date' column (together with other columns). Example, I have 66 rows of 5/1/2017 (May 1, 2017) data, 10 rows of 5/2/2017 (May 2, 2017) data, and so on and so forth 'til 5/31/2017 (May 31, 2017). Now, this file is being processed every first week of the following month (so for this, it would June). Because this is End-of-Month file processing, output should get only the last/latest date available in the CSV file (in this case, 5/31/2017). Regardless if it 5/1/2017, 5/2/2017, 5/3/2017, etc... it should be replaced by 5/31/2017 to still include them in the output.

Also, I would want to change the format of the processed 'Date' into dd/mm/yyyy (31/05/2017).

You've already been given all of the relevant information on Scan, Long and Subst. Using those you can split up the date in the csv file into day, month and year components and recombine them however you want.

Specifically you can extract the month component of the date, and then just use the last date of that month. (30th for September, April, June and November, 28 for February (unless leap years matter) and 31st for anything else.)

You increment the values in the Data tab, adding all of the rows to the element that represents the last day of the month in your time dimension(s).

Also you should make sure that you're aware of how dates work in TM1, although in this case all you need to know is what you've already been told about how to parse strings, and to use the pipe ( | ) operator to put the component strings back in the order that you want them.

You may also want to consider asking the person who is supplying the .csv to make sure that it's in the format that you want, but since (based on what you've said, at least) you only need to parse the first row to work out the date it's pretty much academic. You'd have to parse the date anyway so it really doesn't matter what format it comes in as, as long as it's consistent.