Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cliff_clayman
Creator II
Creator II

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
rubenmarin

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.

View solution in original post

11 Replies
sunny_talwar

Why not just this:

Date(Floor([Posting Date]))

or

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

cliff_clayman
Creator II
Creator II
Author

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.

rubenmarin

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
Creator II
Creator II
Author

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!

rubenmarin

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
Creator II
Creator II
Author

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

rubenmarin

And the preceding load option?

cliff_clayman
Creator II
Creator II
Author

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`;

rubenmarin

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.