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,

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?

Yes, sorry period is the month. The values would be 1-12 for Period and 2006 and up for Full Year.

Is Period 01 through 12? If so why is it an A11? How are you manufacturing the YYM field?

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

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
 

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

So I need to change FY_CD to an integer? But I can't concatenate Integers?

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.

ah, ha! Genius!

Thank you!

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

Thanks to Babak to have clarified it

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

lol I promise I read it, just needed an example to understand why you're multiplying the year by 100.

Thanks!

Don't forget to edit your first post then adding [SOLVED] at the beginning of the subject, once it's going to be solved

How about converting it to a date format via DEFINE, then using the defined field?

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 ?