topic Re: Date load format in QlikView

Date load format

Lloyd — Sat, 16 Nov 2024 02:43:23 GMT

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"

Re: Date load format

sunny_talwar — Fri, 16 Aug 2019 15:09:43 GMT

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

Re: Date load format

MindaugasBacius — Fri, 16 Aug 2019 11:58:20 GMT

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/InterpretationFunctions/date_hash.htm

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

Re: Date load format

Lloyd — Fri, 16 Aug 2019 14:29:06 GMT

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?

Re: Date load format

jonathandienst — Fri, 16 Aug 2019 14:55:27 GMT

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

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

Re: Date load format

sunny_talwar — Fri, 16 Aug 2019 14:55:17 GMT

Can you share how your environmental variables are set?

Re: Date load format

Lloyd — Fri, 16 Aug 2019 15:03:48 GMT

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

Re: Date load format

sunny_talwar — Fri, 16 Aug 2019 15:09:12 GMT

My bad... try this

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

Re: Date load format

Lloyd — Fri, 16 Aug 2019 15:18:36 GMT

Fantastic, solved!

Thanks for great assistance