topic Re: Dates pulled as text from Table View in QlikView

Dates pulled as text from Table View

Fri, 13 Feb 2015 21:06:47 GMT

Hello, Community-

The database access I have is to Views, not to the Oracle database itself. (Don't know if this is relevant or not).

I'm trying to create a report off of a view that has the field "Period" - it's an SQL varchar string field, not a date. The string only captures Month and Year .... Jan-15, Feb-15, etc.

I've tried Dual, Date#, and other posted community solutions, both in the script and the sorting, and no matter what I do, my dates still sort as alpha (Apr-02, Aug-01, Dec-01, Feb-02, Jan-02...)

Can anyone help? Does being required to pull from views make a difference in general?

Thanks

Re: Dates pulled as text from Table View

sunny_talwar — Fri, 13 Feb 2015 21:10:21 GMT

You can extract the 3 letter month name using the left function and then use mapping load to convert Jan to 1, Feb to 2 and so on and then you can create a date with the month names using MakeDate Function.

This might be a long way to do a easy thing, but don't know any better way to do it.

Best,

Re: Dates pulled as text from Table View

Fri, 13 Feb 2015 21:13:02 GMT

Hi,

Can you try following expression DATE#(Period,'MMM-YY')

Regards

Nandkishor S. Pandirkar

Re: Dates pulled as text from Table View

Anonymous — Fri, 13 Feb 2015 21:14:33 GMT

Date# is my preference. Maybe you did something wrong? Try

date(date#(Period, 'MMM-YY'), 'MMM-YY')

It has nothing to do with it being an Oracle view.

Re: Dates pulled as text from Table View

maxgro — Fri, 13 Feb 2015 21:16:23 GMT

=date(Date#('Jan-15', 'MMM-YY'))

=date(Date#(YourOracleField, 'MMM-YY'))

Re: Dates pulled as text from Table View

sunny_talwar — Fri, 13 Feb 2015 21:16:55 GMT

msolomov, this is totally unrelated, but would you be able to tell me what is a difference between DATE function and Date# function. I have seen a lot of posts which discuss the difference and explain them in details, but never really understood the difference.

Thanks in Advance,

Best,

Re: Dates pulled as text from Table View

Anonymous — Fri, 13 Feb 2015 21:22:21 GMT

The date# tells how to read the data, that is date#(Period, 'MMM-YY') says "Read the first three characters as month, and the two after dash as year.

The date() function says how to represent the data, so date(today(), 'WWWW, MMMM DD YYYY') returns Friday, February 13 2015 - no matter what is the default format of the date is used.

Regards,

Michael

Re: Dates pulled as text from Table View

Fri, 13 Feb 2015 21:26:47 GMT

This didn't do it, unfortunately.

Re: Dates pulled as text from Table View

sunny_talwar — Fri, 13 Feb 2015 21:26:53 GMT

That totally makes sense. Thanks for your time on explaining the difference between the two.

Best,

Re: Dates pulled as text from Table View

Fri, 13 Feb 2015 21:27:17 GMT

BINGO!!!!!!!!! I think I left out something when I did date# before, and then fixed the sort in the listbox expression. thanks!