topic Re: Date -10 in QlikView

Date -10

pandreozzi — Mon, 23 Oct 2017 17:06:57 GMT

Hi All. I would like to extract table date using a date but the key is if the table data is not older than 10 days I would like to drop that. In essence 11+ days is when I need to bring the table date into my chart. The reason for the -10 is as an example taking into consideration today's date minus 10 days than start pulling table data. I hope this makes sense.

Any help is appreciated.

Re: Date -10

MarcoARaymundo — Mon, 23 Oct 2017 18:06:33 GMT

Try ...

LOAD

Fields

FROM [TABELA]

WHERE DateField >= (Today() - 10)

Re: Date -10

pandreozzi — Tue, 24 Oct 2017 11:50:12 GMT

Thanks Marco. I just found that the date is stored at text. Can anyone show me how to use the string to date function in the select statement to convert the text version of the date to a real date. The current format is:

DD-MMM-YY

I would like to convert it to

MM-DD-YYYY

Re: Date -10

tresB — Tue, 24 Oct 2017 11:53:46 GMT

Use date#() to covert the string/text to proper date and date() to format it to desired format. Try like:

Date(Date#(DateField, 'DD-MMM-YY'), 'MM-DD-YYYY')

Re: Date -10

pandreozzi — Tue, 24 Oct 2017 12:01:35 GMT

Tresesco, thanks for your reply. However this does not work in the select statement only on the load statement. I need to convert this out of the DB as I follow up with a case statement that will use the date in the findings.

Re: Date -10

tresB — Tue, 24 Oct 2017 12:05:42 GMT

Select statement gets executed in your DB rather than qv. Therefore, you may use the similar date parsing functions that your DB supports, like in Oracle TO_DATE().

Re: Date -10

pandreozzi — Tue, 24 Oct 2017 12:09:47 GMT

agree as I am using the followingstr_to_date(a.date, '%d-%m-%y'), '%d/%m/%y') NEWNAME,

str_to_date(a.date, '%d-%m-%y'), '%d/%m/%y') NEWNAME,

This does not seem to work and I have also tried:
date_format str_to_date(a.date, '%d-%m-%y'), '%d/%m/%y') NEWNAME,

Re: Date -10

tresB — Tue, 24 Oct 2017 12:19:57 GMT

I am not expert in MySql, but it seems that abbreviated month is parsed there by '%b'. Try like:

str_to_date(a.date, '%d-%b-%y')

And yes, be careful about brackets.

Re: Date -10

pandreozzi — Tue, 24 Oct 2017 12:31:00 GMT

ok. I will try that and let you know. I took my format off stackoverflow web site

Re: Date -10

pandreozzi — Tue, 24 Oct 2017 12:50:27 GMT

Tresesco, that worked. who knew that d (month) is actually m(month) appreciate your assistance.