topic Dates in Text Objects in QlikView

Dates in Text Objects

Fri, 24 Sep 2010 16:25:35 GMT

Hi,

I've been reading around and haven't been able to find any information on this, so i'll give this a shot.

I would like to create a label via the Text object that displays something along the lines of

'Information from (start date) to (end date) for so and so'

I've put this:

date#(@10,'MM/DD/YYYY') as [Date of Service], Month(@10) as Month, Year(@10) as Year,
date(DayStart(@10), 'MM/DD/YYYY') as SDay,
date(DayEnd(@10), 'MM/DD/YYYY') as EDay,

in my script, but when I enter this

='Data dates from ' & [SDay] & 'to ' [EDay]

into the expression box, I get a null value.

Any idea where I'm going wrong?

Dates in Text Objects

pover — Fri, 24 Sep 2010 16:32:05 GMT

You're missing a & between to and EDay.

='Data dates from ' & [SDay] & 'to ' & [EDay]

You might also need to put min(SDay) and max(EDay).

Regards.

Dates in Text Objects

Fri, 24 Sep 2010 16:34:30 GMT

='Data dates from ' & min( [SDay]) & 'to ' &max( [EDay])

yields

"Data dates from to "

It doesn't bother displaying the dates.

Dates in Text Objects

pover — Fri, 24 Sep 2010 16:38:47 GMT

If you make a list of SDay, what values do you get?

Also, are the values aligned to the left or the right? If they are aligned left they are text and if they are aligned right they are numbers. You need them to be numbers.

Regards.

Dates in Text Objects

Fri, 24 Sep 2010 16:40:46 GMT

Well it appears that there are no values in the text box either. Date of Service is importing in as text, but it is showing.

I guess there's a conversion method to switch it from text to numeric then convert it back into a date?

Dates in Text Objects

pover — Fri, 24 Sep 2010 16:46:38 GMT

In you script, try the following:

date(date#(@10,'MM/DD/YYYY')) as [Date of Service],
Month(date(date#(@10,'MM/DD/YYYY'))) as Month,
Year(date(date#(@10,'MM/DD/YYYY'))) as Year,
DayStart(date(date#(@10,'MM/DD/YYYY'))) as SDay,
DayEnd(date(date#(@10,'MM/DD/YYYY'))) as EDay,

A load of a load might be even better so that you don't have to repeat date(date#(@10,'MM/DD/YYYY')) so many times.

Regards.

Dates in Text Objects

Fri, 24 Sep 2010 16:57:51 GMT

Still nothing. 😕 The list box doesn't show anything for SDay.

I'm wondering at this point if it's the format the dates are in when imported. They come in as:

05-OCT-09

DD-MM-YY

Dates in Text Objects

pover — Fri, 24 Sep 2010 17:04:41 GMT

Try this then

date(date#(@10,'DD-MMM-YY')) as [Date of Service],
Month(date(date#(@10,'DD-MMM-YY'))) as Month,
Year(date(date#(@10,'DD-MMM-YY'))) as Year,
DayStart(date(date#(@10,'DD-MMM-YY'))) as SDay,
DayEnd(date(date#(@10,'DD-MMM-YY'))) as EDay,

Dates in Text Objects

Fri, 24 Sep 2010 17:11:07 GMT

Ah progress.

That shows the date, but it's got an ugly time stamp next to it now.

"Data dates from 12/7/2009 12:00:00 AM to 2/10/2010 11:59:59 PM"

So I changed my expression to:

='Data dates from ' & date(min( [SDay]), 'MM/DD/YYYY') & 'to ' & date(max( [EDay]), 'MM/DD/YYYY')

and I have

"Data dates from 12/07/2009 to 02/10/2010"

Just to verify, in the script it's specifying the format it's in and not as I'd like it?

Dates in Text Objects

pover — Fri, 24 Sep 2010 17:17:38 GMT

In the function date#() you specify the format it is in to convert the text to date and then in the function date() you you can specify the format to convert it to or if you don't specify a format it will convert to the format defined in the system variables.

Regards.

Dates in Text Objects

Fri, 24 Sep 2010 17:24:35 GMT

Thanks for clearing that up Karl!