Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"
My bad... try this
Time(Frac(TimeStamp#(perfdatetime, 'MM/DD/YYYY hh:mm:ss.fff'))) as [Performance Time];
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);
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:
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?
The time format would normally be 'hh:mm' or 'hh:mm:ss'....
Lower case 'm' minutes, upper case 'M' means months.
Can you share how your environmental variables are set?
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';
My bad... try this
Time(Frac(TimeStamp#(perfdatetime, 'MM/DD/YYYY hh:mm:ss.fff'))) as [Performance Time];
Fantastic, solved!
Thanks for great assistance