×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Combining numbers from two seperate columns

Combining numbers from two seperate columns

Combining numbers from two seperate columns

(OP)
How can i combine two numerical comlumns togeter.  Ie.  the month is 12 and year is 2007, which would equal 12/2007.


Thanks,

RE: Combining numbers from two seperate columns

The first question is, WHAT do you want the result to be?

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;

RE: Combining numbers from two seperate columns

(OP)
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,

RE: Combining numbers from two seperate columns

(OP)
EDIT(AGENCY_SALES_V.AGENCY_SALES_V.MTH, '99') | '/' | EDIT(AGENCY_SALES_V.AGENCY_SALES_V.YR, '9999')


I used this statment A8 and the result for everything is 00/0000.  Why is it not working?


Thanks,
Kerry

RE: Combining numbers from two seperate columns

I would suggest showing AGENCY_SALES_V.AGENCY_SALES_V.MTH and AGENCY_SALES_V.AGENCY_SALES_V.YR, as well as this calculated field, so you can see what INPUT produced this OUTPUT

RE: Combining numbers from two seperate columns

(OP)
I am not quite sure what you mean...  I have been displaying both the month and year and then the combined field and the combined field shows 00/0000.  Can you please tell me why it is showing this?

Kerry

RE: Combining numbers from two seperate columns

Kerry-
What are the Master File Descriptions (MFD) of the fields in question? ie what is the format? Actual and Usage? Length?

This will help us answer your question.

RE: Combining numbers from two seperate columns

(OP)
The month column is an I11 and the year column is I 11 and of course the date in the month column is 1-12 and the year is a 4 digit number.  I hope that is what you were looking for.


thanks,

RE: Combining numbers from two seperate columns

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

RE: Combining numbers from two seperate columns

(OP)
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

RE: Combining numbers from two seperate columns

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

RE: Combining numbers from two seperate columns

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.

RE: Combining numbers from two seperate columns

(OP)
It worked...thank you everyone for your help!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close