Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

cliff_clayman
New Contributor III

Format a Date field in a Straight Table

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?

1 Solution

Accepted Solutions

Re: Format a Date field in a Straight Table

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.

11 Replies

Re: Format a Date field in a Straight Table

Why not just this:

Date(Floor([Posting Date]))

or

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

cliff_clayman
New Contributor III

Re: Format a Date field in a Straight Table

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

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.

cliff_clayman
New Contributor III

Re: Format a Date field in a Straight Table

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

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.

cliff_clayman
New Contributor III

Re: Format a Date field in a Straight Table

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

And the preceding load option?

cliff_clayman
New Contributor III

Re: Format a Date field in a Straight Table

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

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.

Community Browser