topic Re: Format a Date field in a Straight Table in QlikView

Format a Date field in a Straight Table

cliff_clayman — Wed, 01 Feb 2017 20:52:03 GMT

I am trying to format a date field in a straight table and it is not taking the format. Right now, the format looks like this in the straight table:

1/31/2017 12:00:00 AM

I want it to look like 1/31/2017

I coded the Text Format expression for the Dimension field as such:

=Date(Left([Posting Date],10),'MM/DD/YYYY')

It does not change from the original format. When I add a Text Object and put in the same code, the format changes as expected. What is the issue with how I am doing it in the straight table?

Re: Format a Date field in a Straight Table

sunny_talwar — Wed, 01 Feb 2017 20:57:32 GMT

Why not just this:

Date(Floor([Posting Date]))

Date(Floor(TimeStamp#([Posting Date], 'M/D/YYYY h:mm:ss TT')))

Re: Format a Date field in a Straight Table

cliff_clayman — Wed, 01 Feb 2017 21:01:02 GMT

Neither one of those changed the display format. It seems that nothing I put in the Text Format property seems to work. I did test the Text Color to make sure it worked and it did.

Re: Format a Date field in a Straight Table

rubenmarin — Wed, 01 Feb 2017 21:22:40 GMT

Hi Cliff, from your posts I think you are using the format option of the dimension tab, that option is to set bold, italic, etc... format of the text font, not the data.

The data format for dimensions can to be done:

- In the script, while loading the data.

- In the Numbers tab of document properties.

- Using a calculated dimension.

- ...

You only need one of those, the first option is usually the better.

Re: Format a Date field in a Straight Table

cliff_clayman — Wed, 01 Feb 2017 21:30:58 GMT

I ended up using a calculated dimension as I do a Load * in my script and didn't want to list out all the fields!

Re: Format a Date field in a Straight Table

rubenmarin — Wed, 01 Feb 2017 21:51:52 GMT

If it's a resident load you can rename field before load, using the renamed field to create the field as a date and then drop the renamed field.

This can be also done with preceding load

Load *

Date(... as newField;

Load *

from...;

drop oldField;

rename newField to oldField;

This way you will have a date field instead of a text field, it will be a date in every spot is used, and gives better performance than using a calculated dimension.

Think if it's worth the effort, usually it does.

Re: Format a Date field in a Straight Table

cliff_clayman — Wed, 01 Feb 2017 21:53:39 GMT

It's not a resident load. It's just a connection to an Access database and I pull all the fields from it.

Re: Format a Date field in a Straight Table

rubenmarin — Wed, 01 Feb 2017 21:55:23 GMT

And the preceding load option?

Re: Format a Date field in a Straight Table

cliff_clayman — Wed, 01 Feb 2017 22:09:43 GMT

I couldn't get it to work properly.

LOAD *,

Date([Pstng Date]) AS [PostingDate],

If([ICB Flag]='x','Yes','No') as Flag_ICB;

LOAD *

FROM

ODBC CONNECT32 TO [MS Access Database;DBQ=D:\MyDatabase.accdb];

DROP [Pstng Date];

RENAME [PostingDate] to [Pstng Date];

SQL SELECT *

FROM `MyTable`;

Re: Format a Date field in a Straight Table

rubenmarin — Wed, 01 Feb 2017 22:33:50 GMT

I don't have an access db to test the preceding load but maybe:

LOAD *,

Date(Floor(TimeStamp#([Pstng Date], 'M/D/YYYY h:mm:ss TT'))) AS [PostingDate],

If([ICB Flag]='x','Yes','No') as Flag_ICB;

LOAD *

FROM

ODBC CONNECT32 TO [MS Access Database;DBQ=D:\MyDatabase.accdb];

Just to commet: This is not so important if you are using this for self-consume and you are not planning to use that date in other operations.

Re: Format a Date field in a Straight Table

cliff_clayman — Wed, 01 Feb 2017 22:39:24 GMT

This doesn't work. Also, I don't see where you rename and drop the field.

Re: Format a Date field in a Straight Table

rubenmarin — Wed, 01 Feb 2017 22:42:46 GMT

I only copied that part, but the rest should be as it was in your previous post.

Anyway, keep the calculated dimension if it works.