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  WebFOCUS/FOCUS Forum on Focal Point     Smart Date YYM problem

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Smart Date YYM problem
 Login/Join
 
Member
posted
We are building a data warehouse calendar month dimension table and I want a month column in YYM format based on an integer field in the database table. This works fine with YYMD formats but seems to have a bug with YYM formats (or is it I just don't understand something here?). For the most part the returned ccyy/mm values look good .. but not always. Here is example output and build code:

PAGE 1
ID DT_YYM
1 2008/02
2 2008/03
3 2008/04
4 2008/05
5 2008/06
6 2008/07
7 2008/08
8 2008/09
9 2008/10
10 2008/11
11 2008/12
12 2009/01
13 2009/02
14 2009/03
15 2009/04
16 1913/01
17 1913/02
18 1913/03
19 1913/04
20 1913/05
21 1913/06
22 1913/07
23 1913/08
24 1913/09
25 1913/10
26 1913/11
27 1913/12
28 2010/05
29 2010/06
30 2010/07
31 2010/08
32 2010/09
33 2010/10
34 2010/11

SQL SQLMSS
CREATE TABLE [DW_Test].[dbo].[test_yym]
(
ID int identity NOT NULL,
DT_YYM integer NULL,
)
END
SQL SQLMSS
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1286)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1287)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1288)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1289)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1290)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1291)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1292)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1293)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1294)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1295)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1296)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1297)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1298)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1299)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1300)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1301)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1302)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1303)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1304)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1305)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1306)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1307)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1308)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1309)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1310)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1311)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1312)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1313)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1314)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1315)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1316)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1317)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1318)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1319)
END


FILENAME=test_yym ,SUFFIX=SQLMSS ,$
SEGNAME=TEST_YYM ,SEGTYPE=S0 ,$
FIELD=ID ,ALIAS=id ,USAGE=I11 ,ACTUAL=I4,$
FIELD=DT_YYM ,ALIAS=dt_yym ,USAGE=YYM ,ACTUAL=I4,$



SEGNAME=TEST_YYM ,
TABLENAME=DW_Test.dbo.test_yym ,
CONNECTION=testwh ,
KEYS=1 , $



TABLE FILE TEST_YYM
PRINT *
END

Tried this on (with identical results):
WebFOCUS Version 7.6.7 (Gen branch767:155)
Windows Server 2003 R2
Microsoft SQL Server 2005

and on
WebFOCUS Version 7.6.8 (Gen branch768:141)
Ubuntu 8.10
PostgreSQL 8.4


8.0.0.6, Ubuntu 12.04, All
 
Posts: 12 | Location: South Africa | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
Try changing the ACTUAL format for your date column to DATE:

FIELD=DT_YYM ,ALIAS=dt_yym ,USAGE=YYM ,ACTUAL=DATE,$


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
Thanks for the response, Dan.

Unfortunately ACTUAL=DATE does not work. It reads the field but the output is drastically wrong:

 
PAGE 1
ID 	DT_YYM
1 	1903/07
2 	1903/07
3 	1903/07
4 	1903/07
5 	1903/07
6 	1903/07
7 	1903/07
8 	1903/07
9 	1903/07
10 	1903/07
.. etc
 


This appears to me to be a bug but I can't believe no one has picked it up since it would have been wrecking reports for a year now.


8.0.0.6, Ubuntu 12.04, All
 
Posts: 12 | Location: South Africa | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
Since you said YYMD works, you could define the integer column as YYMD and add a DEFINE to the master to convert that date to YYM.

FILENAME=test_yym ,SUFFIX=SQLMSS ,$
SEGNAME=TEST_YYM ,SEGTYPE=S0 ,$
FIELD=ID ,ALIAS=id ,USAGE=I11 ,ACTUAL=I4,$
FIELD=DT_YYMD ,ALIAS=dt_yymd ,USAGE=YYMD ,ACTUAL=I4,$
DEFINE DT_YYM/YYM = DT_YYMD ; $


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
That is a possibility .. since the grain of the dimension table needs to be month rather than day we would have to take the integer associated with the first day of each month and store that. Pesky thing!

In the mean time I've relented and reverted to using I6YYM as the USAGE with ACTUAL=I4. Takes me back 20 years! But I might try your suggestion since smart dates are so much better.

Thanks for the help.


8.0.0.6, Ubuntu 12.04, All
 
Posts: 12 | Location: South Africa | Registered: February 07, 2007Report This Post
Member
posted Hide Post
One problem with the DEFINE field workaround is that SQL aggregation no longer works.

Can anyone else confirm whether they use Calendar Month dimension tables and have encountered this problem on smart dates? The timescale of my project deliverables does not allow waiting for IB to fix this (if its a bug) but if its just me then maybe I misunderstand something. Otherwise I need to log it for the greater good .. just want to check before I take it further.

Thanks All


8.0.0.6, Ubuntu 12.04, All
 
Posts: 12 | Location: South Africa | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
FIELD=DT_YYM ,ALIAS=dt_yym ,USAGE=YYM ,ACTUAL=I4,$

I don't understand how a four-digit integer gets converted to a six-digit date.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Roy, can you store your SQL date column with a date or date-time format? Then there wouldn't be any problems with the translation to a WebFOCUS date or with SQL aggregation.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
I agree with you Francis.

Looks like the integer is number of number of months since 1900.

How does WF converts that to a date ????

I also agree with Dan, it would be safer to store the date in a date or date time field.

Have a look at the code below. It shows what happend when you convert an integer to a date, YYMD or YYM.

EX -LINES 4 EDAPUT MASTER,tmp_int,CV,FILE
FILENAME=TMP_INT, SUFFIX=FIX,$
SEGNAME=TMP_INT, $
  FIELD=INT,ALIAS=  ,USAGE=I9 ,ACTUAL=A9,$
 
EX -LINES 6 EDAPUT MASTER,tmp_date,CV,FILE
FILENAME=TMP_DATE, SUFFIX=FIX,$
SEGNAME=TMP_DATE, $
  FIELD=INT_I9  ,ALIAS=  ,USAGE=I9  ,ACTUAL=I4,$
  FIELD=INT_YYMD,ALIAS=  ,USAGE=YYMD ,ACTUAL=I4,$
  FIELD=INT_YYM ,ALIAS=  ,USAGE=YYM  ,ACTUAL=I4,$
 
-RUN
 
FILEDEF TMP_INT  DISK tmp_int.ftm 
FILEDEF TMP_DATE DISK tmp_int2.ftm (LRECL 12 RECFM F
 
-RUN
 
-REPEAT INT_LOOP FOR &Cntr FROM 1 TO 1400 ;
-WRITE TMP_INT &Cntr 
-INT_LOOP
 
TABLE FILE TMP_INT
PRINT INT
      INT
      INT
ON TABLE HOLD AS TMP_INT2
END
-RUN

TABLE FILE TMP_DATE
PRINT INT_I9
      INT_YYMD
      INT_YYM
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
quote:
I don't understand how a four-digit integer gets converted to a six-digit date.
Is not ACTUAL=I4 is how WF hold this value inside and is be actually held binary? This be holding many digits not 4?

Am I right in thinking this, I not sure now Frowner

Kofi


Client Server 8.1.05: Apache; Tomcat;Windows Server 2012
Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
 
Posts: 106 | Registered: April 06, 2009Report This Post
Expert
posted Hide Post
Actual=I4 does mean that the data is stored as a binary.

But the example at the top writes 4 digit numbers to the database, then reads them as dates, this is the issue.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
As Francis always say - check documentation - this be showing -
quote:
In Binary integers:

I1 = single-byte binary integer.

I2 = half-word binary integer (2 bytes).

I4 = full-word binary integer (4 bytes).
I be happier now Smiler

Kofi


Client Server 8.1.05: Apache; Tomcat;Windows Server 2012
Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
 
Posts: 106 | Registered: April 06, 2009Report This Post
Platinum Member
posted Hide Post
quote:
the example at the top writes 4 digit numbers to the database
Waz,

This I saw but maybe misunderstood the Francis question. It seem be asking on ACTUAL=I4 and I need to be sure my understand.

I am thinking that you are right in data be number of months since base date - perhaps Roy confirm, although I4 to YYM seem to mean this, yes?

This be the case then maybe DEFCENT and YRTHRESH be important, no?

Kofi


Client Server 8.1.05: Apache; Tomcat;Windows Server 2012
Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
 
Posts: 106 | Registered: April 06, 2009Report This Post
Expert
posted Hide Post
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1286)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1287)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1288)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1289)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1290)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1291)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1292)

I don't understand how a four-digit integer gets converted to a six-digit date.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Francis,

The same way that 36525 gets translated to 31/12/1999 in Excel.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Tony, thanks for that. 36525 does indeed get transformed to 1999/12/31 when formatted as a date yyyy/mm/dd. 1286 gets transformed to 1903/07 when formatted as yyyy/mm, which is the behaviour that Roy D mentions, so, if Excel does it this way it must be the right way.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Well, it seems that an integer is an integer when "used" as such (USAGE). However, when using a different data type (date-based for instance) WebFOCUS has a way to interpret it:

USAGE=YYMD, ACTUAL=I4

The number is treated as a DATE and it's interpreted as the number of days since Dec. 31, 1900.

USAGE=YYM, ACTUAL=I4

Here the field is calculated as the number of months since Dec.31, 1900.

I could not find the documentation piece that explain how a date is calculated as an offset based on Dec.31, 1900 (well, I'm probably not searching hard enough) but I'll keep trying.

By the way, I tried USAGE=YY thinking that it may probably be interpreted as a number of years since Dec. 31, 1900 but it actually returned the same integer value.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
quote:
I don't understand how a four-digit integer gets converted to a six-digit date.

Francis, this document says in page 21:
quote:
SmartDate fields are actually stored as an elapsed interval relative to the base-date of
December 31, 1900. As a result January 1, 1901 has a stored value of 1. Dates with values
before this reference date count in a negative direction and dates after this reference date count in
a positive direction.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
This newsletter also has an interesting article about the same topic under the FOCUS Functions and Julian Dates section.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Member
posted Hide Post
Thank you for all the points mentioned thus far.

When we started building the dimension tables for our star-schemas we began, as is pretty standard, with a day-level calendar dimension. Typically I use the internal number rendered as a smart-date (YYMD etc) by WebFOCUS as the surrogate key as well. This is just a convenience. But I use the same type of number for date columns in the table since it is very direct .. WebFOCUS just needs a USAGE=YYMD on an ACTUAL=I4. As noted by others that means a 4 byte integer field gets stored and the integer is the offset in days from 31 Dec 1900. (Waz put the sort of code we would use to seed the table above in this track).

If you take the following code you'll see that just as day level dates convert to an integer (the afore-mentioned offset) so do months:

  
TABLE FILE DIM_ALL_CALENDAR_DAY
IF CALDAY EQ 20100511
PRINT CALDAY
COMPUTE TESTMTH/YYM = CALDAY ;
COMPUTE I_CALDAY/I9 = CALDAY*1 ;
COMPUTE I_TESTMTH/I9 = TESTMTH*1 ;
END


  
<TABLE BORDER CELLPADDING=1>
<TR>
<TD COLSPAN=4>
<TABLE CELLPADDING=0 WIDTH="100%"><TR>
<TD>
PAGE     1</TD></TR></TABLE></TD>
</TR>
<TR>

<TD VALIGN=BOTTOM>
Calendar Day</TD>
<TD VALIGN=BOTTOM>
TESTMTH</TD>
<TD ALIGN=RIGHT VALIGN=BOTTOM>
I_CALDAY</TD>
<TD ALIGN=RIGHT VALIGN=BOTTOM>
I_TESTMTH</TD>
</TR>
<TR>
<TD>
2010/05/11</TD>

<TD>
2010/05</TD>
<TD ALIGN=RIGHT>
39943</TD>
<TD ALIGN=RIGHT>
1313</TD>
</TR>
</TABLE>


This result leads one to think that you can also use an integer with USAGE=YYM on top to represent a month. And this works .. but not always. My question is why not? To be fair to IB I don't think it is documented. But the integers loaded into SQL Server (above) came from WebFOCUS using something like this code snippet. And within the confines of a program they get respected. It is just that if you put them in a database and then try to read them back again they don't get respected.

When I have a moment I'll try the ACTUAL=DATE suggestion.

Thanks again for your thoughts.
Roy


8.0.0.6, Ubuntu 12.04, All
 
Posts: 12 | Location: South Africa | Registered: February 07, 2007Report This Post
Member
posted Hide Post
<TABLE BORDER CELLPADDING=1>
<TR>
<TD COLSPAN=4>
<TABLE CELLPADDING=0 WIDTH="100%"><TR>
<TD>
PAGE     1</TD></TR></TABLE></TD>
</TR>
<TR>

<TD VALIGN=BOTTOM>
Calendar Day</TD>
<TD VALIGN=BOTTOM>
TESTMTH</TD>
<TD ALIGN=RIGHT VALIGN=BOTTOM>
I_SCALDAY</TD>
<TD ALIGN=RIGHT VALIGN=BOTTOM>
I_TESTMTH</TD>
</TR>
<TR>
<TD>
2010/05/11</TD>

<TD>
2010/05</TD>
<TD ALIGN=RIGHT>
39943</TD>
<TD ALIGN=RIGHT>
1313</TD>
</TR>
</TABLE>

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


8.0.0.6, Ubuntu 12.04, All
 
Posts: 12 | Location: South Africa | Registered: February 07, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Smart Date YYM problem

Copyright © 1996-2020 Information Builders