Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Combining numbers from two seperate columns
- Thread starter kfr104
- Start date
The first question is, WHAT do you want the result to be?
For example, if you want an ALPHA string, a DEFINE would work:
On the other hand, if you wanted a 'date' field, you could do the following:
By having the format as a 'legacy' date, the slash is added automatically, be default.
Once you have a 'legacy' date, you can convert it to a 'smart date' by assignment:
For example, if you want an ALPHA string, a DEFINE would work:
Code:
DATE/A7 = EDIT(MONTH) | '/' | EDIT(YEAR);On the other hand, if you wanted a 'date' field, you could do the following:
Code:
DATE/I6YM = 10000 * MONTH + YEAR;By having the format as a 'legacy' date, the slash is added automatically, be default.
Once you have a 'legacy' date, you can convert it to a 'smart date' by assignment:
Code:
SDATE/YM = DATE;- Thread starter
- #3
I have a column that is 1-12 which equals a month and then I have a column that has the year. I am trying to combine them, so that it will be month/year. I have tried to figure out how to do it in the painter, but everything I have tried does not work. What is the best function to use to combine the columns...
Thanks,
Thanks,
- Thread starter
- #4
- Thread starter
- #6
- Thread starter
- #8
Yes - that is the info that I was seeking.
When you use EDIT(AGENCY_SALES_V.AGENCY_SALES_V.MTH, '99'),
you are telling WF to get the FIRST two bytes of the field, hence the zeroes in your answer. This being a numeric field, the information is right justified and you would want the last two bytes.
E.G. EDIT(AGENCY_SALES_V.AGENCY_SALES_V.MTH, '$$$$$$$$$99')
The $ tells WF to omit or skip the first nine bytes in the field and use the last two.
HTH
When you use EDIT(AGENCY_SALES_V.AGENCY_SALES_V.MTH, '99'),
you are telling WF to get the FIRST two bytes of the field, hence the zeroes in your answer. This being a numeric field, the information is right justified and you would want the last two bytes.
E.G. EDIT(AGENCY_SALES_V.AGENCY_SALES_V.MTH, '$$$$$$$$$99')
The $ tells WF to omit or skip the first nine bytes in the field and use the last two.
HTH
- Thread starter
- #10
I have tried the first two formulas you gave me and this is what I get
The first one I recieve:
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: DATE
When checking the second formula I recieve this error
(FOC253) INVALID FORMAT SPECIFICATION ON LEFT HAND SIDE: I6YM
I just do not understand what is going on... All I want is to combine the fileds so they read MM/YYYY....
Please help...
Thanks
The first one I recieve:
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: DATE
When checking the second formula I recieve this error
(FOC253) INVALID FORMAT SPECIFICATION ON LEFT HAND SIDE: I6YM
I just do not understand what is going on... All I want is to combine the fileds so they read MM/YYYY....
Please help...
Thanks
Hi Kerry,
this may help.
First convert your I fields to Alpha, then use Edit to extract the components you need.
DEFINE FILE XXXX
AMONTH/A11 = EDIT(MTH);
AYEAR/A11 = EDIT(YR);
MTHYR/A23 = EDIT(AMONTH,'$$$$$$$$$99') || '/' || EDIT(AYEAR,'$$$$$$$9999');
END
this may help.
First convert your I fields to Alpha, then use Edit to extract the components you need.
DEFINE FILE XXXX
AMONTH/A11 = EDIT(MTH);
AYEAR/A11 = EDIT(YR);
MTHYR/A23 = EDIT(AMONTH,'$$$$$$$$$99') || '/' || EDIT(AYEAR,'$$$$$$$9999');
END
The formulae provided ASSUMED the month was TWO digits, and the year was FOUR digits. As Jimster06 said, if your fields are 11 digits long, you have to use 'edit with mask', with '$' representing 'skip' character in this position, and '9' meaning take character in this position. The EDIT function, inserts LEADING ZEROs, and right justifies the numeric portion of the field. So, if you field was I11, with a value of 3, the EDIT would give 11 characters (the original length), with the '3' in the rightmost position, and all the leading characters would be ZERO.
- Thread starter
- #13
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question