$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: false, vro: false, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
0

How to extract a isoweek from a date input parameter in calculation view

Mar 31, 2017 at 09:56 AM

67

avatar image

Hi all,

I'm trying to create an isoweek calculated columns based on a date in an input parameter.

So here is my input parameter : "add_days(now(), -7)", i declare it in direct parameter with date in type of data (i have try with date/date, date/timestamp, date/nvarchar(8)). We can call him "last week'. For me this input parameter is correct and work.

But i want to extract the isoweek of this date and it's here that i failed. For this, i try to create a calculated columns (because input param and filters can't accept isoweek function), but impossible to validate this columns... SAPHANA always telling me i'm trying to use a non date data... And i'm unable to create my columns that i need to make a filter on my projection... Even try to use aggregator but no success...

Hope i'm clear and you can help me.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Florian Pfeffer
Apr 03, 2017 at 12:24 PM
0

With 2.1.16 I think you mean the version of your installed SAP HANA studio. According to SAP note 2375176 this HANA studio version is compatible with HANA revisions <= 1.00.102.03 (HANA 1.0 SPS10/03) and HANA studio versions are not upwards compatible, I assume you are at a HANA DB version 1.0 <= SPS10.

I do not know exactly anymore which SQL functions were supported in SPS10 for calculated columns, but I think that the functions used by you were not supported, cause most of the function support was added in SPS11 and SPS12 ++.

From my point of view on your system version, you have to create a table function which creates the value by SQLScript and use the table function as data source in a projection node which then can be joined to your other data.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks for your advice, i have obtain the sps version, we are on SPS11 according to our Customer.

I'll look at the note you notice. I have find an another way to do what i want, but i used the table M_TIME_DIMENSION with a lot of JOIN. It's heavy but it's work.

But i'm sure that your solution is rigth too.

0
Florian Pfeffer
Mar 31, 2017 at 11:02 AM
0

It is very hard to realize what issue you really have, with the description you gave in your question text.

I assume that you have a calculated column using SQL language in which you wanna use the "isoweek" function to get the ISO week text string for an input parameter of type date.

This works w/o any issue. The only issue is that the validate function in HANA Studio or the web-based tools raise an error because of an incompatible type, but that error can be ignored because it is a wrong error.

isoweek('$$IP_DATE$$')

In case you have another issue than described, please add more information to your question (maybe with error messages and screenshots, because - as you know - sometimes pictures show more than thousand words :-)).

Best Regards,
Florian

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Thanks for your return Florian, i think i have a bad approach of the problem, SAP HANA work differently from all Tools i have seen before.

We can forget input parameters because my prob comes from function. Here is what i'm unable to do :

I'm in the creation of calculated columns, with "DATE" or "NVARCHAR(8)" in data's type. But no way to make it

work...


ijxn8.png (14.8 kB)
0

What HANA version/revision you are using? Test it on a HANA 1.0 SPS12 and HANA 2.0 SPS00 system w/o issues (in a calculated column with SQL language)?

0

We actually use SAP HANA 2.1.16 (i see this in help menu). Our client can't update the software because all of the project have some probs to upgrade. (red flag everywhere as they tell me)

0