Calendar month and year from date in HANA

[srikanthbwbw]

Hi,

I created analytical view on the sales table VBAP and vbak.

Following are my requirements

1. I have a created date in vbak and I need to derive Calendar month and calendar year from the date..How can i do that in HANA??

2. I have actual delivery date and GR dates in the purchasing tables. Now i need to  calculate the days difference in HANA. I tried daysbetween function but its not working.

3. After looking at the raw data in data preview. Is it possible to remove columns. I mean slice and dice?

Thanks
Sri

[srinivasan_vinayagam · Accepted solution]

Hi Sri,

You will get the answer below wiki.

Regards,

V Srinivasan

[srikanthbwbw · Accepted solution]

Hi Guys,

Thanks for your replies.

@srinivas: I have gone thru that documents but they simply map DATS to DATS so its easy. In my case DATS are converted to VARCHAR in hana.

Saif and sumeet I successfully completed in calculating difference in dates.

I couldnt complete the other one ie deriving calweek, calmonth from date.

As you suggested i create a calculated column in Analytical view of M_TIME_DIMENSION.

Properties of Str_date

Now created Analytical view and tried to join BUDAT to str_date but mapping not taking place.

Can you guys please let me know where i am going wrong.

Thanks

Sri

[former_member210482 · Accepted solution]

Hi Sri,

Are you sure, budat and string date has the date in the same format? Can you cross check their data.

Regards,

Safiyu

[former_member210482 · Accepted solution]

Hi sri,

Not the data type. I meant the date format. ideally it should be yyyy-mm-dd on both the sides. If there is a change, then sumit roy has suggested the workaround to bring it to proper format. Change it in your view.

Regards,

Safiyu

[srikanthbwbw · Accepted solution]

Hi Saif,

BUDAT in HANA is of type VARCHAR and datais 20150121 (YYYYMMDD) and STR_DATE is of type varchar and lenght 8 and data is 2015-01-22  (YYYY-MM-DD) and the funciton is STRING (DATA_SQL)

So the datatype is VARCHAR but data is in different format.

Thanks

Sri

[former_member210482 · Accepted solution]

Hi Sri,

That is the reason why it is not working as expected. Change your string date field to yyyymmdd. Problem Solved.

Thanks,

Safiyu

[srikanthbwbw · Accepted solution]

Hi Saif,

Thanks for your help.

Following is my data format for Date_SQL

2015-01-21

Following is the code

date(leftstr(string("DATE_SQL"),4)+midstr(string("DATE_SQL"),4,2)+rightstr(string("DATE_SQL"),2))

Following is my error

[303]: invalid DATE, TIME or TIMESTAMP value: search table error:  [6931] attribute value is not a date or wrong syntax

Can you please tell me where I am going wrong. I tried with data type "VARCHAR" and "DATE"

Thanks

Sri

[former_member210482 · Accepted solution]

Hi Sri,

You dont have to typecast to date. It should be in string format. The expression should be

leftstr(string("DATE_SQL"),4)+''+midstr(string("DATE_SQL"),6,2)+''+rightstr(string("DATE_SQL"),2)

And also calculated columns data type should be varchar. And in semantics define it as attribute. Join both the fields.. Voilaaa.... Hope your problem is solved...

Regards,

Safiyu

[srikanthbwbw · Accepted solution]

Hi Saif,

I am not getting the required result.

Actually STR_DATE will pick up the data from DATE_SQL and the format of DATE_SQL data in data preview is "Jan 1, 2015" so what i am getting in STR_DATE is 20155-001.

I am unable to understand how str_date is 20155-001 when leftstr=JAN 1. Your help is really appreciated.

Thanks

Sri

[former_member210482 · Accepted solution]

Hi sri,

It displays as Jan 1, 2015. But the actual data will be in the format 2015-01-01 . Use the expression which i have provided. will work for sure.

leftstr(string("DATE_SQL"),4)+''+midstr(string("DATE_SQL"),6,2)+''+rightstr(string("DATE_SQL"),2)

Regards,

Safiyu

[srikanthbwbw · Accepted solution]

Hi Saif,

Really appreciate your patience and help.

Actually my dates in HANA is not VARCHAR but NVARCHAR. Sorry

So I changed STR_DATE data type to NVARCHAR and changed to the code that you have. Activated and data is perfect in data preview.

Now in Analytical view I created data foundation on EKBE table and dragged Attribute view (Time dimensions) to logical view. When trying to join my date to STR_DATE, Joining not taking place. Is there any different way to joing NVARCHAR???

Thanks

Sri

[former_member210482 · Accepted solution]

Hi Sri,

There is nothing as different way for nvarchar. Dont know why you are facing this issue. Try this way. Right click on budat. from menu chose create join. then in the popup chose the view and select str_date. Also chose the type of join..

Still if you are facing the issue share the screenshot.

Regards,

Safiyu

[srikanthbwbw · Accepted solution]

Hi Saif,

I tried the way you mentioned. I get error msg that "Join not supported for calculated column".

Thanks

Sri

[former_member210482 · Accepted solution]

Hi Sri,

That is because calculated column generates data in runtime. So logically there is no data exists to join to that column. Analytical view doesnt allow you to join to calculated column. Try to create it in calculation view. join it there. It will do.

Cheers,

Safiyu

[srikanthbwbw · Accepted solution]

Hi Saif,

I have not worked on calculation view much. What i did is,

1. Drag 2 projections to right and added Date attribute view to one projection and Analytical view to other projection.

2. Selected join and attached both the projections.

Here i created calculated column. Now I dont undestand how mapping takes place.

Can you please guide me.

Thanks

Sri

[former_member210482 · Accepted solution]

hi sri,

No not this way. forget about the analytical view. Drag table to one projection. And time dimension view with the calculated column you created to another projection. In join node join date to str_date. check for semantics and activate..

Regards,

Safiyu

[srikanthbwbw · Accepted solution]

Hi Saif,

You are legend. Thanka mate for your patience and responses. It works perfectly.

Regards

Sri

[former_member210482 · Accepted solution]

Hi Sri,

Your Welcome

Cheers,

Safiyu

[hrsuprith]

I was also stuck in similar situation : This is how I have solved this problem :

Year is actually derived from DATE which is of the following format 2011-01-01.

Output :

Hope this helps.

[Former Member]

Hi Sri,

1) Alternative to Safiyudeen's solution you can also try component function in calculated column.

component(date,x) x=1 for year(yyyy), x=2 for month(mm) and x=3 for date(dd).

But for that date format should be yyyy-mm-dd.

If your date format is not as above mentioned, there is a workaround.

Say your date is 20-06-2014 (dd-mm-yyyy).

You can create a calculated column (say, new_date and data type should be date)

Use below formula

date(rightstr(string('20-06-2014'),4)+'-'+midstr(string('20-06-2014'),4,2)+'-'+leftstr(string('20-06-2014'),2))

Now new_date is in desired date format (yyyy-mm-dd)

Use this new_date calculated column in the component function to fetch calender year and month.

2) Daysbetween function will only work if your date format is 'yyyy-mm-dd'. Can you please check what is the error you are getting. If the format is different you can use above workaround.

3) As said by Safiyudeen, you can mark it hidden in semantics or you don't add to output un-necessary columns in analytic view

Regards,

Sumit

[srikanthbwbw]

Hi Guys,

Thanks for the replies.

I started working 1st option: Mapping date to DATE_SQL field in Analytical view.

Steps:

1. Created Attribute view(ZAV_DATE1) on M_TIME_DIMENSION and selected few fields. one of them is DATE_SQL

2. Created analytical view on EKET table. Selected few fields for output and EINDT is one of the field selected.

2. Dragged ZAV_DATE in logical view and mapped EINDT to DATE_SQL.

When validated I am getting error:

Data Type of the columns in the join Data Foundation.EINDT(NVARCHAR)_ZSK_HANA::ZAV_DATE.DATE_SQL(DATE) are incompatible in Logical Join node

Here what i dont udnerstand is EINDT is of type DATS in SAP Table EKET. But in data foundation its saying varchar.

1. When caling table from SAP, I guess date format will be YYYYMMDD???? Even thou it displayes MMDDYYY?

2. Why HANA is showing EINDT as VARCHAR?

Can you guys please help me?

Thanks

Sri

[former_member210482]

Hi sri,

1) while replicating the data to hana using slt/bods, they would have chosen varchar for date field. Date format in HANA can be either yyyy-mm-dd or yyyy/mm/dd. It can also take values like yyyy-mm/dd or yyyy/mm-dd.

2) To join it to your view, create a calculated column with data type as varchar. In your calculated column do typecasting to string(date). Also make sure the calculated column has the same date format as in source table..

You can also do a work around in source table by generating a new column in your source table. But it will lead to additional physical data storage. So its better to do it in your view. Or Even you can modify your data provisioning methods.

Regards,

Safiyu

[srikanthbwbw]

Hi Saif,

Thanks for your reply. I just spoke to my basis guy and he told me that HANA is installed as database for ECC so we didnt use any slt or BODS.

I asked y dats are being converted to varchar into HANA and the answer is its default configuration of SAP.

I am not sure what he means....Can you please let me know if you have answer.

Thanks

Sro

[former_member210482]

Hi Sri,

Yes, as per SAP standard mapping, DATS field in source will be mapped to nvarchar in HANA. These are default mapping established. Normally when you are using SLT, we override these default mapping by performing individual type mapping.These individual type mapping will be defined in table IUUC_REPL_TYPMAP . So to override we used to change the dbtype field in this table.

Regards,

Safiyu

[srikanthbwbw]

Hi Saif,

Thanks for the answer.

As you mentioned earlier

To join it to your view, create a calculated column with data type as varchar. In your calculated column do typecasting to string(date). Also make sure the calculated column has the same date format as in source table..

I created calculated column in analytical view. Now how do I map this calculated column to DATE_SQL field. In logical view data foundation I cant see calculated column.

How to achieve this.

Thanks

sri

[former_member210482]

Hi sri,

Not in analytical view. in your time dimension you need to create calculated column based on date_sql field.. it will be nothing but string(date_sql). Now that you have date with varchar type it will be easier to join this field with corresponding date field in source table.

Note: Make sure your calculated column has date exactly in the format of your source field. Sumit has already suggested the workaround for it.

Regards,

Safiyu

[former_member226419]

Hi Sri,

I just ran scenario for option 2. what i did I created 2 more calculated columns and convert both the dates of varchar into date () with help of this function and then called daysbetween() and it worked for me.

Let me know if this can solve your query.

BR

Sumeet

[former_member210482]

Hi sumeet,

Yes it will work. But why do you have to create two more calculated column. Its duplicating the fields. Instead you can create it in a single calculated column using the below expression.

daysbetween(longdate(2012-01-01),longdate(2014-01-01))

Regards,

Safiyu

[former_member210482]

Hi sri,

1) You can derive month and year by creating a time dimension table by generating time data and then joining your date field to this table.

2)To calculate days difference you can use daysbetween function.

Note: The field's data type must be date. To be on the safer side try typecasting to longdate(). something like this

daysbetween(longdate(2012-01-01),longdate(2014-01-01))

3)If you mean to play with output columns, you can make use of analysis tab in the data preview. Where you can select your desired columns for the output.

if you mean to restrict or hide some columns you can choose to hide the columns by checking hidden in semantics.

Hope this helps you.

Regards,

Safiyu