Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Try
Month( makedate( YearField, MonthNumber)) as Month
Much cleaner - thanks!
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.
???
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?
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.
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.
Awesome - will try that.
So, it creates another column that can be selected in the sort?