topic Re: Can I create distinct Month & Year from loaded data ? in QlikView

Can I create distinct Month & Year from loaded data ?

Fri, 06 Sep 2013 19:51:36 GMT

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.

Re: Can I create distinct Month & Year from loaded data ?

Anonymous — Fri, 06 Sep 2013 20:00:04 GMT

Try this:

month(monthstart(doc_date)) as Month

year(yearstart(doc_date)) as Year

Regards,

Michael

Re: Can I create distinct Month & Year from loaded data ?

whiteline — Fri, 06 Sep 2013 20:00:38 GMT

LOAD distinct

Month(doc_date) as Month

Resident Data;

Re: Can I create distinct Month & Year from loaded data ?

Fri, 06 Sep 2013 20:06:48 GMT

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

Re: Can I create distinct Month & Year from loaded data ?

Larry_Aaron — Fri, 06 Sep 2013 20:09:13 GMT

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

];

Re: Can I create distinct Month & Year from loaded data ?

Anonymous — Fri, 06 Sep 2013 20:11:40 GMT

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

Re: Can I create distinct Month & Year from loaded data ?

Fri, 06 Sep 2013 20:22:55 GMT

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

Re: Can I create distinct Month & Year from loaded data ?

rwunderlich — Sat, 07 Sep 2013 00:24:35 GMT

Why the MonthStart() and the YearStart()?

-Rob

Re: Can I create distinct Month & Year from loaded data ?

Sat, 07 Sep 2013 03:52:43 GMT

try this in load statement

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

Re: Can I create distinct Month & Year from loaded data ?

Anonymous — Mon, 09 Sep 2013 21:34:49 GMT

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

Re: Can I create distinct Month & Year from loaded data ?

rwunderlich — Tue, 10 Sep 2013 19:51:17 GMT

Hi Michael,

I would disagree that either Month() or MonthStart() could be used here. It depends on how you want to group things. Month() returns only the month, not the year. MonthStart includes the year, and therefore is different.

Month('01/01/2013') = Month('01/01/2012')

MonthStart('01/01/2013') <> MonthStart('01/01/2012')

-Rob

Re: Can I create distinct Month & Year from loaded data ?

Anonymous — Thu, 12 Sep 2013 15:43:46 GMT

Hi Rob,

Certainly these functions are quite different, I've never said they are the same. The month() function returns just a month name(number) from Jan(1) to Dec(12), while the monthstart() is the full date which can be formatted in any way. So, month() often can be repalced with monthstart() with the appropriate formatting ('MMM'), at least for the presentation purpose, but not the other way around.
If Jayadevan V wants just the month name, any of these function can be used. If he wants month and year, month() can't be used but monthstart() can. Personally, I prefer using this for months:
date(monthstart(DateField),'MMM-YY') as MonthYear

Regards,
Michael