Loading Month Values from Excel File

[Qlik_dev03]

I am trying to load dates coming from an Excel file. The field name in Excel is Month. The values are set to a custom format where it shows the date as (MMM-YY) for example "Jan-23". I wan to load the Jan portion of the value into Qlik as Month. When I try to so, qlik makes the values into 3 digits like 450 rather then the 3 letters. 

Here is what I was placing in the data load editor:

LOAD
Left("Month",3) as Q_Month

[Pierrick · Accepted solution]

Hi,

you can try this : 

LOAD
    Month("Month") as Q_Month

[PhanThanhSon]

Hi

try this:

Best regards Son

[stevedark]

Hi @Qlik_dev03 

The reason you are getting the result you are getting is that Excel is storing the value as a date, which behind the scenes is the number of days since 1st Jan 1900 (before then are negative numbers). Today the date is 45346, so you can see why the left three characters comes out as it does.

@Pierrick's solution is spot on.

It's worth knowing you can get all kinds of other variations from there also:

Year(Month) as Year,
Month(Month) as [Month Name],
Date(MonthStart(Month), 'MMM-YYY') as [Month Year],
'Q' & ceil(Month/3) as Quarter,

There are many more functions also.

Hope that helps,

Steve