Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Can I create distinct Month & Year from loaded data ?


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.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Try this:

month(monthstart(doc_date)) as Month

year(yearstart(doc_date)) as Year

Regards,

Michael

View solution in original post

11 Replies
Anonymous
Not applicable
Author

Try this:

month(monthstart(doc_date)) as Month

year(yearstart(doc_date)) as Year

Regards,

Michael

whiteline
Master II
Master II

LOAD distinct

     Month(doc_date) as Month

Resident Data;

Not applicable
Author

Thanks a lot. It works. however the sorting happens on alphabetic order

Larry_Aaron
Employee
Employee

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

];

Anonymous
Not applicable
Author

Month() is a dual function by itself, should be sortable by numeric value.

Not applicable
Author


Thanks Michael, now I clicked on Number in Sort tab, it works.  Thanks agan

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Why the MonthStart() and the YearStart()?

-Rob

Not applicable
Author

Hi

try this in load statement

dual(month(date)&year(date),year(date)&month(date)) as Month_Year

Anonymous
Not applicable
Author

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