topic Re: How do I create a date from JAN2013 in QlikView

How do I create a date from JAN2013

Wed, 25 Nov 2020 16:16:04 GMT

My date file that am importing has the Date as JAN2013 how do I make a date from that?

Re: How do I create a date from JAN2013

MindaugasBacius — Fri, 09 Sep 2016 15:39:38 GMT

Like this:

=Date((Date#('JAN2013', 'MMMYYYY'), 'YYYY-MM-DD')

The result looks like that:

Re: How do I create a date from JAN2013

sunny_talwar — Fri, 09 Sep 2016 15:49:02 GMT

Or may be this:

Date(MakeDate(Right(Date,4), Left(Date, 3), 1)) as Date

Replace 1 with whatever date you want to create.

Re: How do I create a date from JAN2013

Anil_Babu_Samineni — Fri, 09 Sep 2016 17:29:32 GMT

What are you expecting

Re: How do I create a date from JAN2013

Tue, 13 Sep 2016 05:40:49 GMT

I am attempting to be able to use a Max Date variable to use in set analysis, and be able to run things like year to date nut just can't get it to work. My Qv skills are a bit rusty

Re: How do I create a date from JAN2013

sunny_talwar — Tue, 13 Sep 2016 19:12:10 GMT

Did you get it to work, or are you still trying to figure this out?

Re: How do I create a date from JAN2013

MarcoWedel — Tue, 13 Sep 2016 20:09:41 GMT

LOAD *,
          Month(MonthYear) as Month,
          Year(MonthYear) as Year;
LOAD Date#(YourDate,'MMMYYYY') as MonthYear
From YourSource;

hope this helps

regards

Marco

Re: How do I create a date from JAN2013

Wed, 14 Sep 2016 04:58:00 GMT

Still trying to get it to work ......

Re: How do I create a date from JAN2013

sasikanth — Wed, 14 Sep 2016 05:52:15 GMT

HI,

It depends on the granularity level of your data ,check weather it is month level or day level.

please do share a sample of your data.

Re: How do I create a date from JAN2013

Wed, 14 Sep 2016 06:00:50 GMT

Data is at Month level, I have attached a small sample set below

			Value
Store A	Product Cat	Jan12	60859
		Feb12	52132
		Mar12	56551
		Apr12	59750
		May12	58010
		Jun12	55000
		Jul12	57081
		Aug12	56238
		Sep12	56196
		Oct12	58242
		Nov12	57266
		Dec12	73807
		Jan13	67466
		Feb13	59402
		Mar13	75607
		Apr13	64579
		May13	62954
		Jun13	68829
		Jul13	58097
		Aug13	58301
		Sep13	59175
		Oct13	63455
		Nov13	64180
		Dec13	105383
		Jan14	88205
		Feb14	78795
		Mar14	96060
		Apr14	108033
		May14	148180
		Jun14	103311
		Jul14	116351
		Aug14	106459
		Sep14	100022
		Oct14	94334
		Nov14	93256
		Dec14	158555
		Jan15	85170
		Feb15	74543
		Mar15	84702
		Apr15	89426
		May15	87764
		Jun15	83988
		Jul15	73914
		Aug15	66165
		Sep15	65115
		Oct15	68060
		Nov15	69751
		Dec15	85937
		Jan16	64784
		Feb16	57578
		Mar16	67451
		Apr16	78640

Re: How do I create a date from JAN2013

Chanty4u — Thu, 15 Sep 2016 10:46:00 GMT

like below,

aa:

LOAD * INLINE [

Date

Jan12

feb2012

];

Ressult:

LOAD *

Date(Date#(Date,'MMMYY'),'DDMMYYYY') AS NEW

Resident aa;

Re: How do I create a date from JAN2013

miguelbraga — Thu, 15 Sep 2016 11:06:56 GMT

Is this what you're looking for?

See the attached file

Regards,

Re: How do I create a date from JAN2013

miguelbraga — Thu, 15 Sep 2016 11:13:27 GMT

Even try this as multiple date formats because you refered first as JAN2013, then Jan12 or Jan-12:

Alt(

Date((Date#(Date, 'MMM-YY')), 'DD/MM/YYYY'), // for case Date comes with Jan-12

Date((Date#(Date, 'MMMYY')), 'DD/MM/YYYY'), // for case Date comes with Jan12

Date((Date#(Date, 'MMMYYYY')), 'DD/MM/YYYY'), // for case Date comes with JAN2013

'Invalid Format' // If date comes with an invalid format

) as Date

Regards,