Monday, February 13, 2012

How to convert a DatekeyNo to date at RPD Level and at OBIEE Answers Part-1

Hi,

Issue : How to convert a DatekeyNo  to  date at RPD Level and at OBIEE Answers

Often We run in to circumstances in OBIEE where we need to present Date_key_no field from database which has data-type number ( For Ex: 20120113)  in to 01/13/2012 date.


Let us see how to convert this filed in to Date(01/13/2012) at the RPD level and answers. When I ran into this issue I refereed couple of OTN Forums and blogs but not able to use any of those solution directly.
So here is the solution that works for me and hopefully for you too.

Answers Level : 

Field  from database :  

Code to use in Answers to convert into Date :


Substring(cast("Attributes"."Hmfinaldate" as character(30))  FROM 5 FOR 2) ||'/'||Substring(cast("Attributes"."Hmfinaldate" as character(30)) FROM 7 FOR 2)||'/'||Substring(cast(“Attributes"."Hmfinal date" as character(30)) FROM 1 FOR 4)

In the above code Attributes"."Hmfinaldate" is table.columnname.

Out Put :  Please see the screenshot below




RPD LEVEL :  

a) Using Existing Logical column concept(BMM LAYER) 


When I use the code here in  BMM layers using logical columns. It is  throwing some Invalid expression. I don't understand what is the problem. May be some issue in the tool (or) Concatenate doesn't work at this level. verified the format but there is nothing wrong with the format.





b) Using Physical columns : I used same above code with reference to physical columns.This code works fine.

SUBSTRING( CAST ("[GDW] GDW PHYSICAL".""."DWSGDW01"."DWV12006_ORD_POM_FACT_A(POM)"."HM_DELIVERY_FINAL_DT_KEY_NO" AS CHARACTER ( 30 )) FROM 5 FOR 2) || '/' ||  SUBSTRING( CAST ("[GDW] GDW PHYSICAL".""."DWSGDW01"."DWV12006_ORD_POM_FACT_A(POM)"."HM_DELIVERY_FINAL_DT_KEY_NO" AS CHARACTER ( 30 )) FROM 7 FOR 2) || '/' ||  SUBSTRING( CAST ("[GDW] GDW PHYSICAL".""."DWSGDW01"."DWV12006_ORD_POM_FACT_A(POM)"."HM_DELIVERY_FINAL_DT_KEY_NO" AS CHARACTER ( 30 )) FROM 1 FOR 4)



 Thanks
Venkat
The Article is also posted at our company blog :  http://blog.aptude.com/blog-1










1 comment:

  1. hey Dude,
    this is sunil....thanks for your post. if i would have a date of birth how to convert from 10-Jan-2012 to 20120401. Pls mail me ASAP.
    sunil dot chandra7979 at gmail dot com

    Appreciate your quick reply.

    Cheers
    Sunil

    ReplyDelete