[CLOSED] Date and Alpha values both in single column as dateformat using excel2k?

June 06, 2017, 09:35 AM

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,

June 06, 2017, 10:25 AM

MartinY

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,

June 06, 2017, 10:47 AM

Dev

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

June 06, 2017, 11:44 AM

MartinY

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.

June 06, 2017, 12:12 PM

Dev

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

June 06, 2017, 01:11 PM

MartinY

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.

June 12, 2017, 07:58 AM

Tamra

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