topic Re: Date stamp in QlikView

Date stamp

Mon, 21 Mar 2016 13:30:49 GMT

Hello,

In the source data is a date stamp given like: 3/8/2016 8:00:00 AM

This has to be transform to only the date 08-03-2016 so I can use it in a chart.

Caution by a date 3/16/2016 is an extra character.

The transformation has to be done in the script.

Can you help me with the script?

Thanks in advance.

Ralph

Re: Date stamp

sunny_talwar — Mon, 21 Mar 2016 14:07:43 GMT

May be this:

Date(Floor(TimeStamp#(DateField, 'M/D/YYYY h:mm:ss TT')), 'DD-MM-YYYY') as DateField

Re: Date stamp

maxgro — Mon, 21 Mar 2016 14:11:29 GMT

load

datefield,

Date(Floor(Date#(datefield, 'M/D/YYYY h:mm:ss tt')), 'MM-DD-YYYY') as datefieldnew

inline [

datefield

3/8/2016 8:00:00 AM

];

Re: Date stamp

Mon, 21 Mar 2016 14:32:28 GMT

Hello Sunny T,

Thank you for you're reaction.

Sorry but it doesn't work.

Re: Date stamp

sunny_talwar — Mon, 21 Mar 2016 14:37:30 GMT

Seems to be working here:

Script:

Table:

LOAD *,

Date(Floor(TimeStamp#(DateField, 'M/D/YYYY h:mm:ss TT')), 'DD-MM-YYYY') as NewDateField;

LOAD * Inline [

DateField

3/8/2016 8:00:00 AM

];

Can you have more than one space between the date and time? You will have to adjust the highlighted part of the expression to match your current timestamp format

Re: Date stamp

Mon, 21 Mar 2016 14:37:41 GMT

Hello maxgro,

Thanks, but it won't work. Can you help me further, below the script.

Value Timestamp is the date i.e. [3/25/2016 8:10:25 AM ]

Data:
LOAD [Technical Designation],
[User Designation],
[System Description],
Parameter,
Value,
Unit,
Status,
[Value Timestamp]
FROM
[Data\Legionella\Installatie Status PX *.xls]
(biff, embedded labels, header is 19 lines, table is [Installatie Status PX Tapwater$]);

load
[Value Timestamp],
Date(Floor(Date#([Value Timestamp], 'M/D/YYYY hh:mm:ss tt')), 'MM-DD-YYYY') as Date2
inline [Value Timestamp];

Re: Date stamp

sunny_talwar — Mon, 21 Mar 2016 14:39:39 GMT

Try it like this:

Data:

LOAD *,

Date(Floor(TimeStamp#([Value Timestamp], 'M/D/YYYY h:mm:ss TT')), 'DD-MM-YYYY') as [Value Date];
LOAD [Technical Designation],
[User Designation],
[System Description],
Parameter,
Value,
Unit,
Status,
[Value Timestamp]
FROM
[Data\Legionella\Installatie Status PX *.xls]
(biff, embedded labels, header is 19 lines, table is [Installatie Status PX Tapwater$]);

Re: Date stamp

pooja_sn — Mon, 21 Mar 2016 14:40:41 GMT

=Date(Floor(TimeStamp#('3/8/2016 8:00:00 AM', 'M/D/YYYY h:mm:ss TT')), 'DD-MM-YYYY')

Re: Date stamp

Mon, 21 Mar 2016 15:07:37 GMT

You're attachment works also by me but in the file it doesn't.

I will attach the qwv file and database

Re: Date stamp

sunny_talwar — Mon, 21 Mar 2016 15:14:31 GMT

Your timestamp did not have seconds:

LOAD *,

Date(Floor(TimeStamp#([Value Timestamp], 'M/D/YYYY h:mm TT')), 'DD-MM-YYYY') as [Value Date];

LOAD [Technical Designation],

[User Designation],

[System Description],

Parameter,

Value,

Unit,

Status,

[Value Timestamp]

FROM

Database.xlsx

(ooxml, embedded labels, header is 19 lines, table is [Installatie Status PX Tapwater]);

Re: Date stamp

Mon, 21 Mar 2016 20:01:14 GMT

it works for one database but I get every week a database with a new date. So I can create a trend.

I drop them into a map and load them with "databasenaam *".

When I load all the databases it doesn't work anymore. I don't get it.

I attached a few databases.

Can you pls help me again.

I'm sorry for the miscommunication.

Re: Date stamp

sunny_talwar — Mon, 21 Mar 2016 20:08:00 GMT

I don't know what the difference is between the dates, they all look the same. See the attached

Script:

Data:

LOAD [Technical Designation],

[User Designation],

[System Description],

Parameter,

Value,

Unit,

Status,

[Value Timestamp],

Date(Floor(TimeStamp#([Value Timestamp], 'M/D/YYYY h:mm TT')), 'DD-MM-YYYY') as [Value Date]

FROM

Database*.xls

(biff, embedded labels, header is 19 lines, table is [Installatie Status PX Tapwater$]);

Re: Date stamp

Mon, 21 Mar 2016 20:42:48 GMT

Thank you again!

Re: Date stamp

sunny_talwar — Mon, 21 Mar 2016 20:43:52 GMT

No problem my friend