Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Lloyd
New Contributor

Date load format

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"

1 Solution

Accepted Solutions

Re: Date load format

My bad... try this

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

Re: Date load format

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
Valued Contributor III

Re: Date load format

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
New Contributor

Re: Date load format

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?

MVP
MVP

Re: Date load format

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Date load format

Can you share how your environmental variables are set?

Lloyd
New Contributor

Re: Date load format

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

My bad... try this

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

Re: Date load format

Fantastic, solved!

Thanks for great assistance