on 2016 Apr 29 2:46 PM
I am bringing in a column in my data that is yyyymm text and creating two additional columns for yyyy and mm in order to join to other tables.
Example of Table:
YYYYMM (text) | YYYY (text) | MM (text) |
---|---|---|
201604 | 2016 | 04 |
I would like to create a date column based on this information. In my last step, I was going to leverage the LastDayOfMonth(<date>) calculation to align the day with the last day of each month.
I have tried three formulas:
1. MakeDate(yyyy,mm,28):
Error Message: Function MakeDate expects parameter 1 of type 'integer', but receives 'string'. Try using the expected parameter type
2. MakeDate(ToNumber(yyyy),ToNumber(mm),28):
Error Message: Function MakeDate expects parameter 1 of type 'integer', but receives 'double'. Try using the expected parameter type
3. ToDate(Concatenate(YYYYMM,28),"yyyy/mm/dd")
The column populates, but it's blank.
The third try looks similar to this thread, but their answer did not work for me:
I was not able to find the convert to integer function (http://scn.sap.com/thread/3898922). Once this was resolved, the MakeDate function worked. Thank you Varun for the support.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Brent,
Please try converting the columns YYYY and MM to integers using the settings and not inside the formula box.
Other approach to create a separate column by concatenating the YYYY, MM, 28 and convert it to a date field using the option above. This can later be used in the LastDayOfMonth formula as it requires a date field.
Please let me know if there are any questions or comments.
Thank you,
Varun Anand
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Varun,
It looks like I have a new issue as I do not have an option to "convert to integer". I only have options to convert text to a number or date/time.
When a column is a number, I only have options to convert to text.
I have scanned the forums, but have not heard anyone else with this issue. Is this a setting I can turn on in order to convert something to an integer?
Brent
Hi Brent,
There is no special setting to display the integer option. I am using Lumira 1.30, please check if you have the latest version.
In spite of this limitation, I would concatenate all the text fields (YYYY,MM,28) and then covert to a date field to resolve your current issue.
Thank you,
Varun Anand
User | Count |
---|---|
71 | |
10 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.