topic Re: Date format in script in QlikView

Date format in script

rdsuperlike — Tue, 27 Jan 2015 19:15:41 GMT

I am trying to display date. I can see it as date format in the table box but when I do the selection it shows the number.

What should I do to the due_dt to get the date values here. I tried

Floor(date(DMND_DUE_DT)) in the script. But vouldnt get the results.

Any inputs?

Re: Date format in script

maxgro — Tue, 27 Jan 2015 19:20:12 GMT

try without floor

Re: Date format in script

chrismarlow — Tue, 27 Jan 2015 19:24:41 GMT

Is your table showing a date because you have formatted it specifically? You can set at a document level by using Settings - Document Properties - Number tab.

Re: Date format in script

Tue, 27 Jan 2015 19:47:34 GMT

I noticed you don't have a format specified in your "date" command.

I have a similar situation; clients login at all times, and 'dlogin' captures the date and time they logged in. One corporate user wanted to be able to select a 'day'. Most days, we have 1000's of client logins, so just giving a list box of "dlogin" wouldn't work. I used:

date(floor(dlogin),'MM-DD-YY') <- that's our standard date representation

Came out perfect in the list box, but when you select a date, you see "4981 of 3756453'.

In your case, I would suggest adding the date format, in whatever format you want, to the expression.

Re: Date format in script

MarcoWedel — Tue, 27 Jan 2015 20:44:34 GMT

Hi,

maybe you just formatted the DMND_DUE_DT (or the result of your Floor(date(DMND_DUE_DT)) expression) as date in some front end objects instead of loading the field as date:

Try to format the field using the Date function in the script.

LOAD Date(DMND_DUE_DT) as DMND_DUE_DT,
    DMND_DUE_DT as DMND_DUE_DT2,
    DMND_DUE_DT as DMND_DUE_DT3
Inline [
DMND_DUE_DT
42196
];

Regarding your script expression:

Floor(date(DMND_DUE_DT))

it calculates the integer part of the numerical value of the DMND_DUE_DT field and returns a number, not a date. The date() function within the Floor() function in the other hand just formats the already exisiting numerical representation of this field and has no effect, because Floor() will still return a number.

So just try the other way around:

Date(Floor(DMND_DUE_DT))

(only makes sense if DMND_DUE_DT is a timestamp instead of a date, i.e. it has a fractional (=time) part)

hope this helps

regards

Marco

Re: Date format in script

MarcoWedel — Tue, 27 Jan 2015 21:08:05 GMT

Hi,

"4981 of 3756453' in your current selections box means 4981 field values of 3756453 existing values are selected.

Showing all those 4981 values otherwise would require a very large box ...

hope this helps

regards

Marco

Re: Date format in script

Tue, 27 Jan 2015 21:28:47 GMT

Please excuse me for giving the impression I didn't understand what "4819 of 365675" meant. I know perfectly well what it means, and I was highlighting that for the original poster's edification, since at the time it appeared he had multiple items on each day. Now that he has explained in more detail what his data looks like, this wouldn't be an issue.

Re: Date format in script

jagan — Wed, 28 Jan 2015 03:58:29 GMT

Hi,

You should not use Floor() as outer function instead you can use like below to round off to date. Floor() will round off to the lower integer and converts it to the number.

LOAD

Date(Floor(Date)) AS Date_Formatted

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Date format in script

engishfaque — Wed, 28 Jan 2015 04:38:19 GMT

Dear Rdsuperlike,

Try these,

date(Num#(YourDateFieldName),'DD/MM/YYYY') as NewDateField

date(Num(YourDateFieldName),'DD/MM/YYYY') as NewDateField

Kind regards,

Ishfaque Ahmed