topic Re: Date Format in QlikView

Date Format

upaliwije — Wed, 09 Dec 2015 10:35:14 GMT

In my data table the field TRN_DATE has data in the following format

42015

42009

42006

and I want to convert the date into following format when loading data

Date(MonthEnd(date#([TRN_DATE],'DD-MMM-YYYY'))) AS [TRN_DATE]

But I d not get desired output but Null value is returned. Pls help me to correct above script

Re: Date Format

sunny_talwar — Wed, 09 Dec 2015 10:36:35 GMT

Try just with date function:

Date(MonthEnd(Date([TRN_DATE))) as [TRN_DATE]

UPDATE: you would use Date#() function if TRN_DATE was actually coming as text but was in your specified format. Right now it is coming as numbers which can be easily converted into Date by using the Date() function.

Re: Date Format

PrashantSangle — Wed, 09 Dec 2015 10:39:12 GMT

Hi,

Date(MonthEnd(TRN_DATE)) as Month_TRN_DATE

Regards

Re: Date Format

tresB — Wed, 09 Dec 2015 10:39:35 GMT

Try like:

Date(MonthEnd(TRN_DATE), 'DD-MMM-YYYY') as TRN_DATE

Re: Date Format

tresB — Wed, 09 Dec 2015 10:40:23 GMT

Date() (in red) is redundant here.

Re: Date Format

PrashantSangle — Wed, 09 Dec 2015 10:41:42 GMT

No need to use date or date# function in MonthEnd()

Regards

Re: Date Format

sunny_talwar — Wed, 09 Dec 2015 10:42:04 GMT

Agreed

Re: Date Format

roger_stone — Wed, 09 Dec 2015 10:43:42 GMT

All you need is this:

Date(MonthEnd([TRN_DATE]),'DD-MMM-YYYY') AS [TRN_DATE]

Re: Date Format

sunny_talwar — Wed, 09 Dec 2015 10:44:19 GMT

Since both of you mentioned it, do you believe that it takes extra system resources for having it there? Cause for when I code, I like putting it there to make the code more readable and understandable for myself. But if it does take system resources, I might change my habit

Re: Date Format

nagarjuna_kotha — Wed, 09 Dec 2015 10:49:26 GMT

Try this:

>>Date(MonthEnd(TRN_DATE), 'DD-MMM-YYYY') as TRN_DATE

-Nagarjuna

Re: Date Format

Anonymous — Wed, 09 Dec 2015 10:51:26 GMT

or simply:

Date([TRN_DATE],'DD-MMM-YYYY') AS [TRN_DATE]

Re: Date Format

tresB — Wed, 09 Dec 2015 10:53:09 GMT

You are calling a function to do something (here just formatting) - it must take some system resource to do (process) 'that something'. It could be negligible, but it surely does.

Re: Date Format

sunny_talwar — Wed, 09 Dec 2015 10:53:24 GMT

I think he wants MonthEnd of TRN_DATE Balraj. A simple date function won't give him that.

Re: Date Format

Anonymous — Wed, 09 Dec 2015 10:58:47 GMT

As per his/her description:

and I want to convert the date into following format when loading data

I thought he/she just want to format in into 'DD-MMM-YYYY' only...

becuase storing monthend intead of tranxn date, would change the meaning of trxn date

Re: Date Format

PrashantSangle — Wed, 09 Dec 2015 11:04:19 GMT

Hi,

Yes, evaluation on number filed is much efficient than evaluation on date field.

therefore We suggest above solution.

Regards

Re: Date Format

sunny_talwar — Wed, 09 Dec 2015 11:07:51 GMT

So Max you are suggesting that I should be doing something like this in the future?

Date(MonthEnd(Num(DateField))) as DateField

where date field is read as date in MM/DD/YYYY format.

Re: Date Format

roger_stone — Wed, 09 Dec 2015 11:08:01 GMT

It might, but maybe all he wants to do is lump all the months transactions together to get an invoicing date at the end of the month.

Re: Date Format

Anonymous — Wed, 09 Dec 2015 11:12:24 GMT

yep, it all depends what UPALI is looking for

Re: Date Format

PrashantSangle — Wed, 09 Dec 2015 11:27:14 GMT

Hi,

Yes.

Regards

Re: Date Format

upaliwije — Wed, 09 Dec 2015 11:46:21 GMT

Thanks all for Valuable inputs