Month Full Name to Month number conversion in SAP BODS

[Former Member]

Hi Experts,

I am working on SAP BODS. I have a source table which has few fields Time_Key, Day, Month, Year. The month field holds full name of month in each record (Like January, February etc...). I want to map all the source fields to an output template.

Mapping :

Table1 : Source Table

Table2 : Target Template

Time_Key, day, year -> As It Is to Template

Month - > Month number only (Please compare Source Month & Target Fields)

Could you please help me on how to write a corresponding number in target template based on month Full Name in source Month Field?

Thanks in Advance.

--Adithya

[ruprekhadeka123]

Could you plz be clear in requirement that what are your source fields and what are the target fields where you want information ?

You can take as below:

Source Fields (SF) Target fields(TF)

SF1 SF2 SF3 TF1 TF2

Because you need to write separate logic for all.

[Nawfal]

This function I suggested will work for you: month(to_date(source_month_field, 'mon')).

Add it to the field mapping in your query transform and try it. It should work and you don't require full date .

Thanks

[Julian_Riegel · Accepted solution]

Hi there,

You could use multiple approaches to achieve the desired result. The easiest probably would be using a decode function such as:

decode ((MONTH = 'January'), '1',
(MONTH = 'February'), '2',
(MONTH = 'March'), '3',
(MONTH = 'April'), '4',
(MONTH = 'May'), '5',
(MONTH = 'June'), '6',
(MONTH = 'July'), '7',
(MONTH = 'August'), '8',
(MONTH = 'September'), '9',
(MONTH = 'October'), '10',
(MONTH = 'November'), '11',
(MONTH = 'December'), '12',
'Invalid_Source_Value')

Alternatively you could generate yourself an additional field where you concatenate the three input columns and apply the to_date function, such as:

To_date(Day||Month||Year,’DDMONTHYYYY’)

Afterwards, apply the month() function on that concatenated field to retrieve only the months number or do it in one step like:

month(To_date(Day||Month||Year,’DDMONTHYYYY’))

or as nawfal.tazi1 suggested in his above answer - that should work aswell indeed.

regards

Julian

[Abhishek_Hazra]

Hi,
You can use ifthenelse function to define your own rule based on the source data. Please have a look into the following link : https://help.sap.com/viewer/8092b085a68941f6aaa6708685a62b0d/4.2.9/en-US/576227c66d6d1014b3fc9283b0e...

Your syntax would look like somewhat below :

ifthenelse ((MONTH = 'JANUARY'), '01', 
  ifthenelse ((MONTH = 'FEBRUARY'), '02',
    ifthenelse ((MONTH = 'MARCH'), '03',
      ifthenelse ((MONTHN = 'APRIL'), '04',
       ifthenelse ((MONTH = 'MAY'), '05',
        ifthenelse ((MONTH = 'JUNE'), '06',
         ifthenelse ((MONTH = 'JULY'), '07',
          ifthenelse ((MONTH = 'AUGUST'), '08',
           ifthenelse ((MONTH = 'SEPTEMBER'), '09',
            ifthenelse ((MONTH = 'OCTOBER'), '10',
             ifthenelse ((MONTH = 'NOVEMBER'), '11',
              ifthenelse ((MONTH" = 'DECEMBER'), '12',      
          'NON_EXISTENT'))))))))))))

Best Regards,
Abhi

[Former Member]

Thank you so much @abhishek.hazra for writing one more answer for me. Logic looks clear but, i am afraid that each row compared against multiple ifthenelse is going to affect the performance.

[Nawfal]

Hi,

You can use the month() function to represent the input month as a number.

For example:

print(month(to_date(source_field, 'mon')));

This output will be 4:

print(month(to_date('April', 'mon')));

Thanks

[Former Member]

Thanks for a really quick and kind response. But, to_date() function is not needed here. Also month() function converts Full date to month number but i have only Month name as single separate field as shown in screenshots. Please suggest me a conversion function for this case.

PS : I updated the question to make it more clear.

Thanks in advance..