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     [CLOSED] date from A8 to YYMD

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] date from A8 to YYMD
 Login/Join
 
Ronibi
Platinum Member
posted
Hai

working in DB2-environment with Datamigrator GUI.
This is my field in input table:
FIELDNAME=ACTU_DAT, ALIAS=ACTU_DAT, USAGE=A8, ACTUAL=A8
An example is '20190105'

It has to be joined with this field in another table:
FIELDNAME=DAT, ALIAS=DAT, USAGE=YYMD, ACTUAL=DATE
Example is '2019-01-05'

In the source transforms calculator I putted this transformation
DATECVT(ACTU_DAT, 'A8', 'YYMD')

But the inner join gives no result.

Where does it go wrong?

Regards
Ron

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
 
Posts: 115 | Registered: August 29, 2014Report This Post
MartinY
Virtuoso
posted Hide Post
Personally I would do the opposite : convert the date into alpha (A8) and then join the two alpha.
Or convert both into integer date (I8YYMD) or alpha date (A8YYMD) and then join them.

You need to be sure that both field have the same format and content.
You problem is probably there where one field doesn't have the exact same format and content then the other.

Have you print them individually to compare and see if they have the same representation ?


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Ronibi
Platinum Member
posted Hide Post
Hoi Martin

you give three solutions:
- convert the date into alpha (A8) and then join the two alpha
- convert both into integer date (I8YYMD) and then join them or
- convert both into alpha date (A8YYMD) and then join them.

Can you , as a learning option for me, show the three solutions?


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
 
Posts: 115 | Registered: August 29, 2014Report This Post
Addy
Gold member
posted Hide Post
Hi,
Why dont you change the Usage from A8 to YYMD in the master file rather than converting it.
Is there are reaons you want to keep format as A8 in the master file?


WF 8.2.04
Windows/Unix
All Formats
In Focus since 2006
 
Posts: 74 | Location: UK | Registered: September 17, 2018Report This Post
Ronibi
Platinum Member
posted Hide Post
Master file is used in multiple dataflows. I don't know what the effect will be in the other flows by changing the master.


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
 
Posts: 115 | Registered: August 29, 2014Report This Post
Doug
Expert
posted Hide Post
quote:
Master file is used in multiple dataflows

You can add a DEFINE in the synonym and use that without affecting other users...




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
MartinY
Virtuoso
posted Hide Post
quote:
Originally posted by Ronibi:
Hi Martin

you give three solutions:
- convert the date into alpha (A8) and then join the two alpha
- convert both into integer date (I8YYMD) and then join them or
- convert both into alpha date (A8YYMD) and then join them.

Can you , as a learning option for me, show the three solutions?

They are just format conversion and may or may not apply to your situation and/or input format.

You can use DEFINE and apply a DATECVT function to move from A8 to I8YYMD par example or to any other specified format
The important thing is that at the end you are comparing two fields with the same format and content representation.

You should first look at individual field content after transformation and ensure that they can be compared.

It can have plenty of solutions, you need to find the proper one for you


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Waz
Expert
posted Hide Post
quote:
quote:
Master file is used in multiple dataflows

You can add a DEFINE in the synonym and use that without affecting other users...



FYI

With Relation DB's, you can have the same field multiple times.

I your case you could have:
FIELDNAME=ACTU_DAT, ALIAS=ACTU_DAT, USAGE=A8, ACTUAL=A8
FIELDNAME=ACTU_YYMD, ALIAS=ACTU_DAT, USAGE=YYMD, ACTUAL=A8


BUT
What is the format of the column in DB2 ?


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
Ronibi
Platinum Member
posted Hide Post
Thnx. The problem in the last answer is that we always do a recreate of a master, not a refresh.
So when I add something in the master, it will be lost once it is recreated because of some other change.

As I said before I already tried DATECVT

In the source transforms calculator I putted this transformation
DATECVT(ACTU_DAT, 'A8', 'YYMD')

But the inner join gives no result


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
 
Posts: 115 | Registered: August 29, 2014Report This Post
Doug
Expert
posted Hide Post
quote:
we always do a recreate of a master
Why do you always do a recreate of a master instead of just refreshing / updating the data?

Does anything change in the master, like fields being added or deleted or format changes?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report 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     [CLOSED] date from A8 to YYMD

Copyright © 1996-2020 Information Builders