Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSED] Type II Begin Date

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Type II Begin Date
 Login/Join
 
noleary
Member
posted
Here is what I am trying to accomplish with a type II dimension. When a new record comes into the dimension i would like to set the begin_date to 1900-01-01 and end_date = 9999-12-31. When a type II field changes for that record I want to set the begin_date = &YYMD and end_date = &YYMD - 1. I have the end dates working properly but I cannot seem to get the begin dates to behave properly.

I have tried setting the variable &&CM_SCDATE
'(IF D.BEGIN_DATE EQ &LO_DATE.EVAL
THEN &&TYPE_II_DT_NAME.EVAL
ELSE IF D.END_DATE EQ &HI_DATE.EVAL
THEN &LO_DATE.EVAL
ELSE &&TYPE_II_DT_NAME.EVAL)'

And I have tried this as well:
'(IF D.END_DATE EQ &HI_DATE.EVAL
THEN &&TYPE_II_DT_NAME.EVAL
ELSE &LO_DATE.EVAL)'
but niether seems to work right.

&LO_DATE = 1900-01-01
&HI_DATE = 9999-12-31
&&TYPE_II_DT_NAME = &YYMD-1


Has anyone attempted something like this? Basically I need to identify if the record is a new or existing record so that I can set the CM__SCDATE correctly.

This message has been edited. Last edited by: Tamra,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 7 | Registered: May 21, 2014Report This Post
Juan Romero
Member
posted Hide Post
Hello noleary,

Try with:

-SET &LO_DATE = DATECVT('19000101','A8YYMD','YYMD') ;
-SET &HI_DATE = DATECVT('99991231','A8YYMD','YYMD') ;

Regards,


FOCUS 7.1.1
OS/390 and Windows
Excel, Txt
 
Posts: 4 | Registered: April 14, 2009Report This Post
noleary
Member
posted Hide Post
That is not my issue. I can get the HI_DATE and LO_DATE to be inserted. What I cannot do is have the LO_DATE inserted in the Begin_Date when it is a new record and &YYMD inserterd in the Begin_Date when it is a changed record.

Here is how I have the variable set in the data flow.

-Big GrinEP_2
-TYPE (ICM18097) DEP_2: Set variables
-DEFAULT &&PROCESS_DATE = &YYMD
-SET &LO_DATE = 19000101;
-SET &HI_DATE = 99991231;
-SET &&TYPE_II_DT_NAME = 'EFF_DATE';
-SET &&CM__SCDEIDT = DATE_B4_UPDATE;
-SET &&CM__SCDEADT = &HI_DATE;
-SET &&CM__SCDATE = '(IF D.END_DATE EQ &HI_DATE.EVAL THEN &&TYPE_II_DT_NAME.EVAL ELSE &LO_DATE.EVAL)';
-GOTO Big GrinEP_MAIN;

EFF_DATE is defined in the flow as &&PROCESS_DATE and DATE_B4_UPDATE is defined as EFF_DATE - 1

My end dates for the Type II work fine. It is the Begin_Date I am struggling with


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 7 | Registered: May 21, 2014Report This Post
Clif
Guru
posted Hide Post
DataMigrator support for Slowly Changing Dimension Type II was designed to assign the "current" date as the begin date for active records.

As you know you can use some other date by settig the global variable &&SCM__SCDATE for re-runs or simply running the flow as though it were some date in the past. This can be easily done in the variables object in a process flow.
Note that I'm sorry we can't support editing the generated focexec; all changes will get lost when you reopen the flow.

So you could initialize the begin date values by running the flow the first time with it set to the turn of the last century. Then for subsequent runs remove the setting and any updates will be assigned the current date.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSED] Type II Begin Date

Copyright © 1996-2020 Information Builders