Matching month column ('MMM') to month extracted from date

[Anonymous]

Hi!

Can someone help me with my load statement?

I am trying to load a column containing only month names written in 'MMM' format so they would be the same as month names in the same format extracted from dates. Currently they don't seem to be matching because I am getting 24 different values in the Month dimenssion.

Thanks!

[Anil_Babu_Samineni]

May be provide sample data?

[sasiparupudi1]

May be try

Date (Date#(MonthField,'MMM'),'MMM')

[omkarvamsi]

Hi,

may be you should use date interpreter function date#() which will tell Qlik view about the date format coming from the source level and use date() to get month name from date.

Please provide the screenshot or sample application for better understanding

Regards

Om

[tresesco · Accepted solution]

Try like:

Month(Date#(1&MonthField, 'YMMM')) as Month

[Kushal_Chawda]

try this

month((Date#(MonthField,'MMM'))

[Anonymous]

Thanks you! Seems to be working just fine. Whats the reason behind this?

[Colin-Albert]

You are most likely not loading your data correctly as a date value and/or are trying to match a date value to text.

These blogs show how to process dates correctly in Qlik.

Why don’t my dates work?

Get the Dates Right

QlikView Date fields

An example of your application or actual load script would help.

[tresesco]

Let me try to explain.

Month() produces dual values, i.e. values that has numeric value at the back and a text in the front. That means, if you see 'Jan' being returned by month(), at the back it has a numeric value 1. You can test it by simply trying Month+1 to get 2 as output or using num(Month) to get 1 when 'Jan' is selected from month field.

Now, month() takes a date field as parameter which should also be a dual value (a number at the back). Since, we wish to get a proper month (in qlik) from text (like, 'Jan', 'Feb'...), we should think of first generating a date from it and then pass it to month(). Date#() being a parsing function, it can produce date from text. We need three values to get a valid date, year, month and day. Date#() can generate date from first two taking the third parameter i.e. day by default as 1. So when I pass '1Jan' to date#() telling format 'YMMM', it actually transform it to '0001Jan1' and parse it as YYYYMMD to get a valid date. Now that date#() passes a valid date, month() does the rest.

Hope this helps.

Edit: Correction: '1Jan' would be taken as '0001Jan1'. had this been two-digit presentation, it would have taken 2000 as year base. I.e. '11Jan' would be taken as '2011Jan1'.