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     [SOLVED]Question for a concatenate function

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Question for a concatenate function
 Login/Join
 
Gold member
posted
I am trying to convert a Hyperion 8.5 to WebFocus. The original Hyperion report use “concat (case, app_date)” to concatenate case number and app_date to get CaseApp as following.

Case App_Date CaseApp
C000001 11/03/14 C0000011414972800000
C000002 04/18/16 C0000021460937600000

Is any way I can get the same result after I concatenate case number and app_date to CaseApp?

Thank you for your help.

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
Expert
posted Hide Post
I'm not sure I understand your question. To concatenate two columns in WebFOCUS, you simply use one of two concatenation operators, | or ||. If the app_date column is really a date or date-time column, you'll have to convert it to a string before concatenation.


Creating Reports With WebFOCUS Language > Using Expressions > Creating a Character Expression > Concatenating Character Strings


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
Gold member
posted Hide Post
Thank you. My problem is I can't get the same result from Hyperion such as "C000001141972800000" from "C000001" and "11/03/14".


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
Expert
posted Hide Post
quote:
141972800000

What is this?


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
Gold member
posted Hide Post
I have no clue either. The original "concat (case, app_date)" from Hyperion generate this.

Thank you.


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
Master
posted Hide Post
It looks like it may be the number of seconds elapsed since January 1, 1970 (Epoch Time).


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Gold member
posted Hide Post
Looks like. But is any way the WebFocus can do the same thing? Thank you.


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
Master
posted Hide Post
Make that milliseconds.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Expert
posted Hide Post
Exactly as Squatch suggests -

TABLE FILE CAR
  SUM COMPUTE ENDDATE/HYYMDS = HINPUT(10,'04/18/2016',8,'HYYMDS');
      COMPUTE BEGDATE/HYYMDS = HINPUT(10,'01/01/1970',8,'HYYMDS');
      COMPUTE DIFF/D20c      = HDIFF(ENDDATE,BEGDATE,'DAY','D20') * 24 * 60 * 60 * 1000;
   BY COUNTRY
END


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
Use the document at the following link to understand datetime (and other) functions.

Also checkout FPRINT as you will need it!

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
Gold member
posted Hide Post
Thank you very much, I'll take look at it. Thank you for your help.


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
Expert
posted Hide Post
Thinkiing about this a little more and you may be able to add a define to your synonym something like -

DEFINE CONCATFLD/A24 = SQL.concat(case, app_date);

You would need to check the SQL produced but this should be more efficient than using COMPUTES or DEFINES to convert date(time) field values.

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
Great suggestion!


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
Gold member
posted Hide Post
Thank you for all your help.


WebFOCUS 8.0.09
WebFOCUS 7.7.05
DBMS: Oracle 11g
all output (Excel, HTML, PDF)
 
Posts: 56 | Registered: June 17, 2011Report This Post
Virtuoso
posted Hide Post
Ed,

You can also use the general DB_EXPR function.
See in the documentation:
"Using Functions > Data Source and Decoding Functions > DB_EXPR: Inserting an SQL Expression Into a Request"


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
quote:
Originally posted by Tony A:
Exactly as Squatch suggests -

TABLE FILE CAR
  SUM COMPUTE ENDDATE/HYYMDS = HINPUT(10,'04/18/2016',8,'HYYMDS');
      COMPUTE BEGDATE/HYYMDS = HINPUT(10,'01/01/1970',8,'HYYMDS');
      COMPUTE DIFF/D20c      = HDIFF(ENDDATE,BEGDATE,'DAY','D20') * 24 * 60 * 60 * 1000;
   BY COUNTRY
END


T


Don't you need to make sure that you're using 01/01/1970 at the UTC time zone? I'm pretty sure that the method above will have issues with DST change-overs and other differences in time zones.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
That might be the case, but then this was only a suggestion on a way forward. Ultimately I was not providing the entire solution but a vehicle to attain what was needed.

However, I would abandon this method and go for the SQL.function method instead.

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
Master
posted Hide Post
quote:
Originally posted by Wep5622:
quote:
Originally posted by Tony A:
Exactly as Squatch suggests -

TABLE FILE CAR
  SUM COMPUTE ENDDATE/HYYMDS = HINPUT(10,'04/18/2016',8,'HYYMDS');
      COMPUTE BEGDATE/HYYMDS = HINPUT(10,'01/01/1970',8,'HYYMDS');
      COMPUTE DIFF/D20c      = HDIFF(ENDDATE,BEGDATE,'DAY','D20') * 24 * 60 * 60 * 1000;
   BY COUNTRY
END


T


Don't you need to make sure that you're using 01/01/1970 at the UTC time zone? I'm pretty sure that the method above will have issues with DST change-overs and other differences in time zones.

Something tells me that the epoch conversion is being combined with the "Case" field to generate a unique identifier. If that's true, then it probably doesn't matter.

And since the "App_Date" is always a date and not a datetime, that's another indicator to me that time is not a factor here, and so time zones wouldn't be either.

Just a little Sherlock Holmes sleuthing on my part (Ahem... I mean "guess"), but I think only EdHou knows for sure.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Virtuoso
posted Hide Post
That's not quite correct I'm afraid.

The results of above code can be different from those generated by Hyperion, namely when the epoch in UTC is on a different date than the one in the local time zone.
That happens every year around the DST change-over if the time difference between UTC and local time is more than 2 or 3 hours, depending on the direction of the change-over and the local time zone offset from UTC.

Even if all that EdHou needs is a unique identifier, the identifiers created when the clock goes back an hour could be duplicates of identifiers created an hour before.

It is unfortunate that WF does not have better time zone support in its datetime format and functions.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report 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     [SOLVED]Question for a concatenate function

Copyright © 1996-2020 Information Builders