topic Re: Date in Month in QlikView

Date in Month

Mon, 05 Aug 2013 13:57:38 GMT

Hi,

I have an excel file with all dates of the year. I dont have the month inside the excel. Is it possible to get the month in QV that i can select them by month with july and not with "7"?

Re: Date in Month

shree909 — Mon, 05 Aug 2013 14:08:09 GMT

Hi ,

Yes its possible create an inline table and join with the month of the excel file

[Calendar]:

LOAD * INLINE [

Month_ID, Month, Quarter

1, Jan,Q1

2, Feb, Q1

3, Mar, Q1

4, Apr, Q2

5, May, Q2

6, Jun, Q2

7, Jul, Q3

8, Aug, Q3

9, Sep, Q3

10, Oct, Q4

11, Nov, Q4

12, Dec, Q4

];

Excel_Files:

JOIN([Calendar])

load Date,

Month(Date) AS Month,

from excelfile..

Here u r joing with the month field to the inline table ,

so by default whaterver the month number u get selected respective month name is shown..

If ur still unclear about this ,

post a sample file ..

Thanks

Re: Date in Month

tresB — Mon, 05 Aug 2013 14:08:42 GMT

I believe you meant month is there but in numeric format, if it is so, you can read the excel date in qlikview like the following (to get the month name):

Load

Date(Yourdatefield, 'DD-MMM-YYYY') as QVDate,

Date(Yourdatefield,'MMM') as Month

From <Excel path>;

Re: Date in Month

Mon, 05 Aug 2013 14:24:48 GMT

The exemple file is just some dates... and the values are random tipped in....

Re: Date in Month

cesaraccardi — Mon, 05 Aug 2013 14:31:52 GMT

Hi,

If you want to just convert from numeric to text representation you can do this way:

Load
     (...),
     Date(Date#(ExcelMonth,'MM'),'MMM') as MonthDescription
FROM
     ExcelFile;

Cheers,

Cesar