Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSED] Type II Begin Date
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Type II Begin Date
 Login/Join
 
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: 3 | Registered: May 21, 2014Reply With QuoteReport This Post
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: 3 | Registered: April 14, 2009Reply With QuoteReport This Post
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: 3 | Registered: May 21, 2014Reply With QuoteReport This Post
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.
 
Posts: 384 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

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

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.