topic Logic help to create date & Time. in QlikView

Logic help to create date & Time.

lalitkgehlot89 — Wed, 25 Nov 2020 16:16:04 GMT

Hi, below given data in text format. I want to convert it into Timestamp (DD-MM-YYYY hh:mm:ss) format.

Please help to convert it.

2018/5/30 0:0:0

2018/5/30 0:10:0

2018/05/30 0:10:0

2018/12/30 0:10:0

2018/1/3 0:10:0

2018/1/3 10:10:15

2018/1/23 10:10:15

2018/10/23 10:10:15

Re: Logic help to create date & Time.

tresB — Wed, 06 Jun 2018 10:15:36 GMT

Try like:

=Timestamp(TimeStamp#(Field,'YYYY/M/DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss')

Please note, you have to use exactly same number of spaces in timestamp#() while mentioning format between DD and hh.

Re: Logic help to create date & Time.

lalitkgehlot89 — Wed, 06 Jun 2018 10:56:30 GMT

Hi Tresesco,

It's not working. whole data is in text format.

Re: Logic help to create date & Time.

tresB — Wed, 06 Jun 2018 10:59:44 GMT

Timestamp#() is being used to convert the text data into proper timestamp (number at the back-end). The concept I showed above is right. If it is not working, that is possibly because the format you are trying with (to convert the text to number) is not matching. you could share a sample file (may be excel or csv).

Re: Logic help to create date & Time.

lalitkgehlot89 — Wed, 06 Jun 2018 11:18:15 GMT

Hi,

Please get the attached data file. First Column is Date Field.

Re: Logic help to create date & Time.

tresB — Wed, 06 Jun 2018 11:25:23 GMT

It seems to be a proper date as excel reads it so. Try like:

Load

Alt( TiimeStamp(Field, 'DD-MM-YYYY hh:mm:ss'),

Timestamp(TimeStamp#(Field,'YYYY/M/DD hh:mm:ss TT'),'DD-MM-YYYY hh:mm:ss')

) as Timestamp

Re: Logic help to create date & Time.

lalitkgehlot89 — Wed, 06 Jun 2018 11:32:41 GMT

No data is coming in Timestamp field after applying the same condition.

Re: Logic help to create date & Time.

vvira1316 — Wed, 06 Jun 2018 12:07:10 GMT

Hi,

I think it is happening for two reason. In your data you have multiline header or Unit word in your time column is causing that column to be treated as text column.

You can also try following

SET TimeFormat='hh:mm:ss TT';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff] TT';

Modified input file as follows

LOAD Name,
[GENERATOR MW]
FROM
[CRITICAL PARAMETER LIST_201805300000-201806050000.xlsx]
(ooxml, embedded labels, table is [CRITICAL PARAMETER LIST_2018053]);

//LOAD Name,
// [GENERATOR MW]
//FROM
//[CRITICAL PARAMETER LIST_201805300000-201806050000.csv]
//(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

Output as follows

//LOAD Name,
// [GENERATOR MW]
//FROM
//[CRITICAL PARAMETER LIST_201805300000-201806050000.xlsx]
//(ooxml, embedded labels, table is [CRITICAL PARAMETER LIST_2018053]);

LOAD Name,
[GENERATOR MW]
FROM
[CRITICAL PARAMETER LIST_201805300000-201806050000.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

Re: Logic help to create date & Time.

tresB — Wed, 06 Jun 2018 12:14:46 GMT

This:

LOAD

Timestamp(TimeStamp#(Unit,'YYYY/M/DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss')as Timestamp,

Unit

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

worked for me:

Re: Logic help to create date & Time.

Anonymous — Wed, 06 Jun 2018 12:51:24 GMT

LOAD Timestamp(Timestamp#(Date,'YYYY/M/D h:m:ss'),'YYYY/M/D h:m:ss') AS Date;

Re: Logic help to create date & Time.

Anonymous — Thu, 07 Jun 2018 12:07:17 GMT

Hi,

Just try Following script,

Timestamp(MakeDate(Year(Date#(SubField(Dates,'/',1),'YYYY')),Month(Date#(SubField(Dates,'/',2),'MM')),KeepChar (Subfield(SubField(Dates,' ',1),'/',3),'0123'))&' '& MakeTime( hour( SubField(Dates,' ',2)),Minute( SubField(Dates,' ',2)),Second( SubField(Dates,' ',2)))) as TimeStamp

My result is as follow

May this will help you