Combing two number fields into a text field.

[#1]

I need to combine two number fields together.  I have two fields.  One is Year and the other is Month.

During the load, I want to create a new field with these two fields.

Year = 2014 Month = 08

The new field will have a new value something like 2014-08

Year &”-“& Month  Does not work.

[#2]

Ok, maybe I am loading wrong.  Here how my load statement looks.

CTW:

BUFFER (stale after 10 hours)

LOAd * ; SQL 

Select

year,

month,

from datatable

:

How should I be loading?

[#3]

What I have read, I have to use the sql load.  I'm using an odbc to load.

[#4]

When I tried this, I get an error saying SQL does not regconize MakeDate as a function.

[stevedark]

As Massimo says you need to do the concatenate in the preceding load, something like this:

LOAD

*,

year & ' ' & month as YearMonth

;

SQL Select

year,

month,

from datatable

:

If you do that though the resultant value will be a string, and will not be ideal for date comparisons, this would be better still:

LOAD

*,

Date(MakeDate(year, month), 'YYYY-MM') as YearMonth

;

SQL Select

year,

month,

from datatable

;

This way the resultant value will be a dual (i.e will have a numeric and text representation) and will sort correctly and be more efficient.

You also need to ensure that you always match the case of field names.

Hope that helps.

Steve

[#6]

Thanks that works.  I now have a second issue, well kind of the same issue.

I'm loading two tables with the new field.  this will be my join between the two tables.  The first table loads fine with the new field.

The second table errors out saying that saying that the Year field can't be found.

So only the 1st table loads with the new field.  The second table does not load.

Both are coming from the same database.

[#7]

Thanks Steve,

now I understand!

I want to use it as a joint between two tables.  I can't use the document date to tie the table together.  The tables need to be tie together on the month and year that the tables are downloaded from a bank website.

[#8]

Thanks everyone for your help on my issue.  I ended up putting the code on the SQL select statement.  Both table now joins together on this new field.

CAST(t0.Year AS char(4)) + '-' +

     case       when len(t0.month) = 1

                    then '0' + CAST(t0.Month AS char(2))

                         else CAST(t0.Month AS char(2))

                    end as Yr_mth_link

Tom