Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have already loaded 2 million records. Is there a way to create year & month from my doc_date ? I have used month(doc_date) which returns the month but in many lines.
Try this:
month(monthstart(doc_date)) as Month
year(yearstart(doc_date)) as Year
Regards,
Michael
Try this:
month(monthstart(doc_date)) as Month
year(yearstart(doc_date)) as Year
Regards,
Michael
LOAD distinct
Month(doc_date) as Month
Resident Data;
Thanks a lot. It works. however the sorting happens on alphabetic order
Use the Dual function.
Load
Dual(Name, Num) As Month;
LOAD * INLINE [
Num, Name
1, Jan
2, Feb
3, Mar
4, Apr
5, May
6, Jun
7, Jul
8, Aug
9, Sep
10, Oct
11, Nov
12, Dec
];
Month() is a dual function by itself, should be sortable by numeric value.
Thanks Michael, now I clicked on Number in Sort tab, it works. Thanks agan
Why the MonthStart() and the YearStart()?
-Rob
Hi
try this in load statement
dual(month(date)&year(date),year(date)&month(date)) as Month_Year
Rob,
I'd rather expected you to ask "why month()"? There is no need to use both month() and monthstart() here. Any one of them should work, I'm not sure why month() didn't work properly at the first place, so suggested to try another approach.
The monthstart() converts all the dates values in one month to one single date. Typically I use this in the script to create "Month and Year" field:
date(monthstart(DateField),'MMM-YY') as MonthYear
Pradas,
The expression above is more or less the same that you create with dual, but it is more effective - shorter, simpler, and utilizes the "natural dualism" of the date function. The only possible downsize that it is if fact a date, the first date of a month - but if you think of it, it is an additional convenience for using in expressions.
Regards,
Michael