Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Finding greater than a date, where the field isn't a date format field..
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Finding greater than a date, where the field isn't a date format field..
 Login/Join
 
Gold member
posted
Hi all,

I'm in need of finding greater than and less than equal to a particular date. The problem is that I have 2 fields I'm concatenating are different formats. I have Full Year, which is format (A6V) and Period with is (I11).

What I had working before the year switched over to 2019, is editing the Period to an A11, and concatenating the Full Year and Period together and was working great. The problem now that the year switched over and it's period 2(FEB), it's showing just Period 12 because 12 is the highest number.

I also tried putting the Full Year first and the Period second in the concatenation. Such as 201902 but I get an error and it doesn't work.

I'm at a loss of what I need to do..

Any help is greatly appreciated!

Thank you

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
It would really help us understand what you're trying to fix if you give us examples of the values in question. By Period I assume you mean Month?


WebFOCUS 8206, Unix, Windows
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold member
posted Hide Post
Yes, sorry period is the month. The values would be 1-12 for Period and 2006 and up for Full Year.


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Is Period 01 through 12? If so why is it an A11? How are you manufacturing the YYM field?


WebFOCUS 8206, Unix, Windows
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
As Babak stated having a complete sample and code may help to figure exactly what you are trying to accomplish and how.

AFAIK when I have to concatenate a Yr and Mth a possible option is the following (assuming that both fields are numeric or converted to numeric value)

YrField * 100 + MthFld

I do prefer to work with numeric fields than alpha especially to perform test such as GE, GT, LE, LT
Because using an alpha string may end you with surprises depending on collation, case sensitivity, leading/trailing spaces, etc. which numeric fields don't mind


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
Here's my code

 -SET &ECHO=ALL;
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';

DEFINE FILE si_bi/hyp_tables/HYP_CP_EAC_ACTUAL_REVENUE
TOTAL_VALUE/D20.4p=HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.SIBI_CTD_REV/HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.SIBI_CV;
PDNO/I11=(PD_NO);
PDNUM/A11=EDIT(PDNO);
YEARMONTH/I10=(PDNUM || HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.FY_CD );
END

TABLE FILE si_bi/hyp_tables/HYP_CP_EAC_ACTUAL_REVENUE
PRINT PCT.TOTAL_VALUE/D8.4% NOPRINT
BY HIGHEST 1 YEARMONTH NOPRINT
WHERE HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.COMPANY_NAME EQ &COMPANY_NAME.(OR(FIND HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.COMPANY_NAME IN si_bi/hyp_tables/HYP_CP_EAC_ACTUAL_REVENUE |FORMAT=A10V)).Company.;
WHERE HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.ORG_NAME EQ &ORG_NAME.(OR(FIND HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.ORG_NAME IN si_bi/hyp_tables/HYP_CP_EAC_ACTUAL_REVENUE |FORMAT=A25V,WITHIN=COMPANY_NAME)).Organization.;
WHERE HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.CUST_NAME EQ &CUST_NAME.(OR(FIND HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.CUST_NAME IN si_bi/hyp_tables/HYP_CP_EAC_ACTUAL_REVENUE |FORMAT=A25V,WITHIN=ORG_NAME)).Customer.;
-*WHERE HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.PROJ_TYPE_CD EQ &PROJ_TYPE_CD.(OR(FIND HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.PROJ_TYPE_CD IN si_bi/hyp_tables/HYP_CP_EAC_ACTUAL_REVENUE |FORMAT=A50V,WITHIN=CUST_NAME)).Project Type.;
-*WHERE HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.SIBI_PROJ_MGR EQ &SIBI_PROJ_MGR.(OR(FIND HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.SIBI_PROJ_MGR IN si_bi/hyp_tables/HYP_CP_EAC_ACTUAL_REVENUE |FORMAT=A58V,WITHIN=PROJ_TYPE_CD)).Project Manager.;
-*WHERE HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.SIBI_COST_ANALYST EQ &SIBI_COST_ANALYST.(OR(FIND HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.SIBI_COST_ANALYST IN si_bi/hyp_tables/HYP_CP_EAC_ACTUAL_REVENUE |FORMAT=A82V,WITHIN=SIBI_PROJ_MGR)).Cost Analyst.;
WHERE HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.SIBI_PROJECT EQ &SIBI_PROJECT.(FIND HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.SIBI_PROJECT IN si_bi/hyp_tables/HYP_CP_EAC_ACTUAL_REVENUE |FORMAT=A50V,WITHIN=CUST_NAME).Project.QUOTEDSTRING;
ON TABLE SUBHEAD
"<DST.TOTAL_VALUE"
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $
TYPE=TABHEADING, LINE=1, JUSTIFY=CENTER, $
TYPE=TABHEADING, LINE=1, ITEM=1, OBJECT=FIELD, FONT='ARIAL', SIZE=28, COLOR=RGB(128 128 128), JUSTIFY = CENTER, STYLE=NORMAL, $
ENDSTYLE
END

-RUN
 


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
YEARMONTH/I10=(PDNUM || HYP_CP_EAC_ACTUAL_REVENUE.HYP_CP_EAC_ACTUAL_REVENUE.FY_CD );

As far as I can see, the above is invalid.
You cannot assign alpha value (PDNUM which is a A11 and I figure that FY_CD is also one, and furthermore the pipe function || is reserved to alpha target, not numeric) to a numeric field (I10)

What are the values/format of FY_CD and PD_NO ?

Please provide sample using the code tag, not the quote tag


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
So I need to change FY_CD to an integer? But I can't concatenate Integers?


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Let's say you have Year as an I4 and Month as an I2. Then YearMonth will be Year *100 + Month. You'll end up with an I field that contains the numeric representation of 201900+2=201902.


WebFOCUS 8206, Unix, Windows
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold member
posted Hide Post
ah, ha! Genius!

Thank you!


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:

posted February 14, 2019 10:43 AM

AFAIK when I have to concatenate a Yr and Mth a possible option is the following (assuming that both fields are numeric or converted to numeric value)

YrField * 100 + MthFld

I do prefer to work with numeric fields than alpha especially to perform test such as GE, GT, LE, LT
Because using an alpha string may end you with surprises depending on collation, case sensitivity, leading/trailing spaces, etc. which numeric fields don't mind

Not that I'm not offensed but it's important scbillions that you read all answers to your question, sometime the solution is already there Big Grin

Thanks to Babak to have clarified it Wink

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


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
lol I promise I read it, just needed an example to understand why you're multiplying the year by 100.

Thanks!


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Don't forget to edit your first post then adding [SOLVED] at the beginning of the subject, once it's going to be solved


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
How about converting it to a date format via DEFINE, then using the defined field?
 
Posts: 3045 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
How about converting it to a date format via DEFINE, then using the defined field?

Also an option, but from what I understood, the first issue was to "merge" the year and the month together that originate from two different fields.
IMHO, why adding a step to have a date when it's not needed and the numeric value do the job ?


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Finding greater than a date, where the field isn't a date format field..

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.