topic Loading Month Values from Excel File in App Development

Loading Month Values from Excel File

Qlik_dev03 — Thu, 22 Feb 2024 14:20:11 GMT

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

Re: Loading Month Values from Excel File

Pierrick — Thu, 22 Feb 2024 14:54:07 GMT

Hi,

you can try this :

LOAD Month("Month") as Q_Month

Re: Loading Month Values from Excel File

PhanThanhSon — Thu, 22 Feb 2024 15:09:14 GMT

try this:

Best regards Son

Re: Loading Month Values from Excel File

stevedark — Sat, 24 Feb 2024 20:49:09 GMT

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