topic Re: Convert a date into months in QlikView

Convert a date into months

mahendragaur — Wed, 25 Nov 2020 16:16:04 GMT

Hello Users,

I'm importing one of the .csv file into QlikView. The data source contains one column in format " 3/21/2016 12:00:00 AM" but I want only month from the mentioned date. I'm using month() function to fetch only months and that I am using every time but it didn't work. Could any one please tell me what may be reason that month function is not working.

below is the script I'm using -

LOAD //WorkCenter_Operation_Desc_Op_No_Key & '-' & SHOP_ORDER & '-' & Num([End Date]) AS CompositeKey,
//WorkCenter_Operation_Desc_Op_No_Key,
//SHOP_ORDER,
Month([End Date]) as Month1,
[LT(hrs)]
FROM
//[$(vExcel_Folder)\ChartData.csv]

(txt, utf8, embedded labels, delimiter is ',', msq, filters(
Remove(Row, Pos(Top, 2))
));

Re: Convert a date into months

sunny_talwar — Mon, 27 Mar 2017 11:06:14 GMT

May be try this

Month(Date#([End Date], 'M/D/YYYY h:mm:ss TT')) as Month1

Also, check these links out

Why don’t my dates work?

Get the Dates Right

QlikView Date fields

Re: Convert a date into months

stonecold111 — Mon, 27 Mar 2017 11:18:57 GMT

date([End Date],'MM') as month

try this. if it don't work

try this one

date(timestamp#([End Date],'DD/MM/YYYY hh:mm:ss TT'),'MM')

Re: Convert a date into months

sunny_talwar — Mon, 27 Mar 2017 11:21:22 GMT

Don't recommend doing that, because this will only display as a date, but will have an underlying date value. So in a list box, this might show 5 31 times if there is a date for each of the days in the month of May.

Re: Convert a date into months

stonecold111 — Mon, 27 Mar 2017 11:25:36 GMT

Yes you are right sunny..

Re: Convert a date into months

stonecold111 — Mon, 27 Mar 2017 11:27:26 GMT

shall we use date# in place of timestamp# ????

as it is in timestamp format.. just for clarification

Re: Convert a date into months

sunny_talwar — Mon, 27 Mar 2017 11:28:13 GMT

Date#() or TimeStamp#() should both work... but TimeStamp#() will be more informative

Re: Convert a date into months

stonecold111 — Mon, 27 Mar 2017 11:30:29 GMT

informative means in what way??? then why we need timestamp# when date# can do the same thing

Re: Convert a date into months

PrashantSangle — Mon, 27 Mar 2017 11:33:02 GMT

There is no such difference in both function You can use which ever you want.

See Henric reply on below thread.

date() vs timestamp()‌

Regards,

Re: Convert a date into months

sunny_talwar — Mon, 27 Mar 2017 11:34:49 GMT

When a new developer or non-developer (with limited knowledge of QlikView) see TimeStamp vs Date... he will have an easier time understanding TimeStamp as a timestamp and date as a date... so for somebody who doesn't know, it might make sense to use TimeStamp#().

then why we need timestamp# when date# can do the same thing

I don't know, do you want to go ahead and send an email to Qlik asking them why they made Date#() and TimeStamp#() interchangeably usable?

Re: Convert a date into months

stonecold111 — Mon, 27 Mar 2017 11:36:04 GMT

thanks

Re: Convert a date into months

mahendragaur — Mon, 27 Mar 2017 11:45:04 GMT

Thank you Sunny.. Perfect answer. It worked

Re: Convert a date into months

stonecold111 — Mon, 27 Mar 2017 11:48:49 GMT

plz make sunny's as correct answer as it would help others