use of a pick() in a load script to substitute month name

[#1]

I have source data, which does not have a date, only a month number and year.

I created an inline table:

Month_Sort:

Load * Inline [

RA_Month, MthSortOrder

Jan,1

Feb,2

Mar,3

Apr,4

May,5

Jun,6

Jul,7

Aug,8

Sep,9

Oct,10

Nov,11

Dec,12

];

.... and then in my transactions load, did this:

pick(MonthNumber,

'Jan',

'Feb',

'Mar',

'Apr',

'May',

'Jun',

'Jul',

'Aug',

'Sep',

'Oct',

'Nov',

'Dec'

) as RA_Month

It works, but is there a better way?

[swuehl]

Try

Month( makedate( YearField, MonthNumber)) as Month

[#3]

Much cleaner - thanks!

[#4]

So, I tried this:

Month( makedate( YearField, MonthNumber)) as Month

For some reason it duplicates Oct Nov and Dec in the list of months.

I then reloaded from the pick() code, and it did not duplicate those months. 

Your code looks fine - looked it up in the date functions.

???

[swuehl]

Strange, I believe it shouldn't duplicate any value.

Are you link the Month field to another table? If so, how do you create the month over there?

Is it possible that you post a small sample file?

[#6]

I do link that Month field:

Month_Sort:

Load * Inline [

RA_Month, MthSortOrder

Jan,1

Feb,2

Mar,3

Apr,4

May,5

Jun,6

Jul,7

Aug,8

Sep,9

Oct,10

Nov,11

Dec,12

];

So, the Month( makedate( @102, @104)) as RA_Month     ...should associate properly.

Whe I get a chance, I will create a small sample.

[swuehl · Accepted solution]

Month() function creates a dual with a numeric representation you can use for sorting. No need for the extra table (which probably causes the problem, because you are linking a dual with a text field).

Try removing your Month_Sort table.

[#8]

Awesome - will try that.

So, it creates another column that can be selected in the sort?