Combining numbers from two seperate columns

[kfr104]

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


Thanks,

 

[focwizard]

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

For example, if you want an ALPHA string, a DEFINE would work:

DATE/A7 = EDIT(MONTH) | '/' | EDIT(YEAR);

On the other hand, if you wanted a 'date' field, you could do the following:

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:

SDATE/YM = DATE;

 

[kfr104]

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,

 

[kfr104]

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

 

[focwizard]

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

 

[kfr104]

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

 

[jimster06]

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.

 

[kfr104]

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,

 

[jimster06]

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

 

[kfr104]

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

 

[craigiep]

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

 

[focwizard]

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.

 

[kfr104]

It worked...thank you everyone for your help!