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 and Alpha values both in single column as dateformat using excel2k?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Date and Alpha values both in single column as dateformat using excel2k?
 Login/Join
 
Dev
Gold member
posted
I have a requirement to do a sort in excel report output, the output column contains both date and alpha values,

below is my code, for displaying both formats we make it as Alpha. but while the user doing a sort option in excel it is not sorting properly, because it is not in date format, how to help user to sort for LPDREQDATE_NEW? using WebFOCUS 8105M

I tried dynamically, like based on the output value we changed the column format (LPDREQDATE_NEW/Q1_1), but the output is showing like "+++++++++" !!!

  
DEFINE FILE HLD1
AAEA/A20       = HCNVRT(NUREQDATE, '(HDMtYY-)', 20, 'A20');
LPDREQDATE_NEW/A25     = IF AAEA EQ '01-Jan-1900' THEN '01-Dec-2015' ELSE AAEA;
Q1_1/A8     = IF LPDREQDATE_NEW NE '01-Dec-2015' THEN 'HDMtYY-' ELSE 'A8MDYY';
END

TABLE FILE HLD1
PRINT
AAEA
LPDREQDATE_NEW
LPDREQDATE_NEW/Q1_1
BY SAM
ON TABLE PCHOLD FORMAT EXL2K
END


Please any one help...

Thanks,
Dev

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


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Report This Post
MartinY
Virtuoso
posted Hide Post
Is this may suits your need ?

Keep the date in date format: no need to put it in alpha to then return it in date format.

DEFINE FILE CAR
IN_DATE    /HDMtYY- = DECODE COUNTRY ('FRANCE' '20150305' 'ITALY' '20150810' 'JAPAN' '20150501' 'ENGLAND' '20151218' ELSE '19000101');
DATE_NEW   /HDMtYY- = IF IN_DATE EQ '01-Jan-1900' THEN '01-Dec-2015' ELSE IN_DATE;
ALPHA_DATE /A20     = FPRINT(DATE_NEW, 'HDMtYY-', 'A20');
END

TABLE FILE CAR
PRINT IN_DATE
      DATE_NEW
      ALPHA_DATE
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN

But keep in mind that Excel will process data that looks like date as a date. So the ALPHA_DATE is processed as a date.

If you want ALPAH_DATE as an alpha for Excel, you need the following:

DEFINE FILE CAR
IN_DATE    /HDMtYY- = DECODE COUNTRY ('FRANCE' '20150305' 'ITALY' '20150810' 'JAPAN' '20150501' 'ENGLAND' '20151218' ELSE '19000101');
DATE_NEW   /HDMtYY- = IF IN_DATE EQ '01-Jan-1900' THEN '01-Dec-2015' ELSE IN_DATE;
ALPHA_DATE /A20     = '''' || FPRINT(DATE_NEW, 'HDMtYY-', 'A19');
END

TABLE FILE CAR
PRINT IN_DATE
      DATE_NEW
      ALPHA_DATE
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN


You will then have a different Excel result if you order by DATE_NEW or ALPHA_DATE using Excel sort feature.

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


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
Dev
Gold member
posted Hide Post
Hi Martin,

Thanks for your reply.

small change is -
DATE_NEW /HDMtYY- = IF IN_DATE EQ '01-Jan-1900' THEN 'Complete' ELSE IN_DATE;

Now we can't use the /HDMtYY format,

After we are getting the output in excel, the above cell contains both 'Complete' and IN_date (Date) value.
Now the user wants to change the sort order using excel sort option. if the column in date format then we can get the sort order properly; if it is in text format then the order will be changed based on the first character.

Hope you understand this...

Thanks,
Dev


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Report This Post
MartinY
Virtuoso
posted Hide Post
You can't have two different format in the same column using Focus (even in Excel where it will sort differently the two formats).

DEFINE FILE CAR
IN_DATE    /HDMtYY- = DECODE COUNTRY ('FRANCE' '20150305' 'ITALY' '20150810' 'JAPAN' '20150501' 'ENGLAND' '20151218' ELSE '19000101');
DATE_NEW   /HDMtYY- = IF IN_DATE EQ '01-Jan-1900' THEN '01-Dec-2015' ELSE IN_DATE;
ALPHA_DATE /A20     = IF IN_DATE EQ '01-Jan-1900' THEN 'Complete'    ELSE '''' || FPRINT(IN_DATE, 'HDMtYY-', 'A19');
END

If you want to sort by alpha value, it's better to have alpha value in the column. And the data will be sorted as per ASCII table character's order.
If you want to sort by the date value, it's better to have date value in the column.
In conclusion, you need two columns.

Sometimes you have to make users understand that what they are asking for is meaningless and if they want it, they need to make compromises such as having two columns that will allow the two sort options.


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
Dev
Gold member
posted Hide Post
Nice Thread

Agree what you said Martin.

I am trying to change the format based on the data (FMT) in the column, I thing this will help to change, but still trying.

I don't have idea but still I have a hope we can achieve this in WF, 
 
DEFINE FILE CAR
IN_DATE    /HDMtYY- = DECODE COUNTRY ('FRANCE' '20150305' 'ITALY' '20150810' 'JAPAN' '20150501' 'ENGLAND' '20151218' ELSE '19000101');
DATE_NEW   /HDMtYY- = IF IN_DATE EQ '01-Jan-1900' THEN '01-Dec-2015' ELSE IN_DATE;
ALPHA_DATE /A20     = IF IN_DATE EQ '01-Jan-1900' THEN 'Complete'    ELSE FPRINT(IN_DATE, 'HDMtYY-', 'A19');
FMT/A7 = IF ALPHA_DATE EQ 'Complete' THEN 'A8' ELSE 'HDMtYY-';
END

TABLE FILE CAR
PRINT IN_DATE
      DATE_NEW
      ALPHA_DATE/FMT
BY COUNTRY
-*ON TABLE PCHOLD FORMAT EXL2K
END
-RUN


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Report This Post
MartinY
Virtuoso
posted Hide Post
quote:

I am trying to change the format based on the data (FMT) in the column, I thing this will help to change, but still trying.

I don't have idea but still I have a hope we can achieve this in WF,
DEFINE FILE CAR
IN_DATE /HDMtYY- = DECODE COUNTRY ('FRANCE' '20150305' 'ITALY' '20150810' 'JAPAN' '20150501' 'ENGLAND' '20151218' ELSE '19000101');
DATE_NEW /HDMtYY- = IF IN_DATE EQ '01-Jan-1900' THEN '01-Dec-2015' ELSE IN_DATE;
ALPHA_DATE /A20 = IF IN_DATE EQ '01-Jan-1900' THEN 'Complete' ELSE FPRINT(IN_DATE, 'HDMtYY-', 'A19');
FMT/A7 = IF ALPHA_DATE EQ 'Complete' THEN 'A8' ELSE 'HDMtYY-';
END

TABLE FILE CAR
PRINT IN_DATE
DATE_NEW
ALPHA_DATE/FMT
BY COUNTRY
-*ON TABLE PCHOLD FORMAT EXL2K
END
-RUN


Forget about that : you can't have more than one format in a single column.
The only way to display multiple "layout" is the way I showed you where each value become an alpha (text).
Using FPRINT function it keeps the layout but displayed as alpha.
But in fact, the column keep the same format for all value since it's a A20 per example. So you do respect the rule with 1 format in the column.


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
Tamra
Guru
posted Hide Post
DEV,

Let us know if Martin's suggestion works for you.

We kindly ask that you update the topic with your solution/further questions as well as add a tag to your subject line.

Focal Point Guidelines

Here is a list of keywords to use:

[SOLVED]
[CODE]
[WORKAROUND]
[CLOSED]
[SHARING]
[CASE-OPENED]

Thank you for participating in the Focal Point Forum!
Tamra Colangelo
Focal Point Moderator
Information Builders


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Report 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 and Alpha values both in single column as dateformat using excel2k?

Copyright © 1996-2020 Information Builders