on 03-29-2013 2:48 AM
Hello,
New to Hana so it might be a stupid question but I have a date attribute in my analytic view and I am trying to create a calculated attribute for 'year' based on the attribute "Date".
I thought it should be simple (and may be it is) but when I define the data type as NAVCHAR 4 and expression as "Date", I get the entire date instead of just first 4 characters. I tried expression leftstr("Date",4) but I get an error. Tried several different options Year("Date") etc. but nothing seems to work.
Can someone help me please?
regards,
Manoj
Hi Manoj,
to extract a part of the date information, please use the component-function.
component ( "DATEFIELD", 1)
will return a 4 character string containing the year-component of the data.
Just as it is documented 😉
- Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ah ---- that's something you should avoid.
First of all, you cannot be sure about the actual format of the output of an date-to-string conversion.
Could be anything like
2001-12-02, 02/12/2001, 12-02-2001 ...
The other thing is, the component function is more efficient than the two stacked functions.
This will be visible if applied to lots of data.
- Lars
Hi Santosh,
not really sure I got your question right.
COMPONENT() does allow to pick different components to be extracted from a data or a timestamp value.
See http://help.sap.com/hana/hana_dev_en.pdf#page=163 for the full function documentation.
- Lars
Before I forget:
In any case, converting/extracting components from a data column is processing intensive and might require row-by-row work, depending on the setup.
With the build-in time system wide time tables (_SYS_BI.M_TIME_DIMENSION...) (http://help.sap.com/hana/hana_dev_en.pdf#page=163 you can solve the same taks via a join.
The system tables already contain extracted components and different date/time formats (if the data had been generated before).
That way, you can just omit single conversions/extractions and join in the date format you need.
- Lars
what function to use if the date is being picked from ECC table??
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.