Help Mapping Date from Number

[#1]

Hi Guy's

Not sure how to do this but i have  tables not joined on date but have numbers like this FREC0001-12, 12 for the year 2012 up to 2015. I would like to map all the numbers with 12 at the end to a year 2012 ect.

Is this possible ?

Regards

[alexandros17 · Accepted solution]

When you load the table create a new field containing the year, if the fields in tha tables have the same name tha tebles will be joined. to do this:

Load

   ....

   myfield

   2000 + Right(myField,2) as myYear

   ....

let me know

[petter]

If you have a field already denoting the year - like for instance Year then this will link your table with the special field to these years:

LOAD

     .......

     myField,

     '20' & Right( myField , 2 ) AS Year,

    ....

FROM

    .....;

myField containing values like 'FREC0001-12' etc...

[Anonymous]

can you share your sample application?

[#5]

Thank you so so much Alessandro

Used

Load

   ....

   myfield

   2000 + Right(myField,2) as myYear

and worked perfectly.

Kind regards

[MarcoWedel]

Hi,

if the presence of the '-' separator is also important, then one solution could be:

LOAD *,
    Year(Date#(SubField(Field1,'-',2),'YY')) as Year
Inline [
Field1
FREC0001-12
FREC0001-13
FREC0002-12
FREC0002-13
FREC0001
FREC0002
FREC0003
];

hope this helps

regards

Marco