Date load format

[Lloyd]

Hi. I’m new to QlikView, and struggling with a date format.

* FYI - I’m running: QlikView x 64 Personal Edition (QlikView April 2019 SR1)

So, I’ve saved some PC performance data, in a csv file. I can load data file successfully. But when I try to load a date from the date timestamp – it returns  blank.

See load statement, and sample records from csv file below.

What should the preceding load statement be, so correctly display a 1) date and 2) time

LOAD *,
     Date#( perfdatetime, 'M/D/YY') as Date, 
     Time(perfdatetime) as [Performance Time];

LOAD [(PDH-CSV 4.0) (South Africa Standard Time)(-120)] as perfdatetime,
     [\\LLOYD-PC\Memory\Available MBytes] as [Available MBytes],
     [\\LLOYD-PC\Processor(_Total)\% Processor Time] as [% Processor Time]

FROM
[D:\Work\QlikView\LLOYD-PC_20190816-000002\DataCollector01.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

"(PDH-CSV 4.0) (South Africa Standard Time)(-120)","\\LLOYD-PC\Memory\Available MBytes","\\LLOYD-PC\Processor(_Total)\% Processor Time"

"08/16/2019 09:48:52.865","3977","18.361718800942505"

"08/16/2019 09:49:07.863","3979","11.300763899761733"

[sunny_talwar]

Try this

LOAD *,
     Date(Floor(TimeStamp#(perfdatetime, 'MM/DD/YYYY hh:mm:ss.fff')), 'M/D/YY') as Date, 
     Time(Frac(TimeStamp#(perfdatetime, 'MM/DD/YYYY hh:mm:ss.fff'))) as [Performance Time];

LOAD [(PDH-CSV 4.0) (South Africa Standard Time)(-120)] as perfdatetime,
     [\\LLOYD-PC\Memory\Available MBytes] as [Available MBytes],
     [\\LLOYD-PC\Processor(_Total)\% Processor Time] as [% Processor Time]
FROM
[D:\Work\QlikView\LLOYD-PC_20190816-000002\DataCollector01.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

[MindaugasBacius]

I guess this should work out:

 Date(Date#(Left(perfdatetime, 10) 'MM/DD/YYYY'), 'M/D/YY') as Date,

 Time(Time#(Mid(perfdatetime, 12, 5), 'HH:MM'), 'HH:MM') as [Performance Time].

To clarify the formulas you should know that Date#, Time# works as an interpretation functions and helps QV to understand the field as a date or time while Date, Time works as formatting functions.

Please check this out:

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/InterpretationFunction...

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/FormattingFunctions/Da...

[Lloyd]

Thanks for the swift response!

The DATE worked like a charm, but the TIME produced an unexpected result. All the records reflect TIME as: 12/30/99

** your script for DATE and TIME look to have the same format i.e. 'MM/DD/YYYY hh:mm:ss.fff')), 'M/D/YY')....

Is this correct, if I need a TIME output?

[jonathandienst]

The time format would normally be 'hh:mm' or 'hh:mm:ss'....

Lower case 'm'  minutes, upper case 'M' means months.

[sunny_talwar]

Can you share how your environmental variables are set?

[Lloyd]

SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='R# ##0,00;-R# ##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYY/MM/DD';
SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff]';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-ZA';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

[sunny_talwar · Accepted solution]

My bad... try this

Time(Frac(TimeStamp#(perfdatetime, 'MM/DD/YYYY hh:mm:ss.fff'))) as [Performance Time];

[Lloyd]

Fantastic, solved!

Thanks for great assistance