topic reading timestamps from a csv in QlikView

reading timestamps from a csv

Sat, 14 Nov 2009 00:17:33 GMT

I am trying to read a timestamp from a csv

blobname, unitblobname. ,blabla,N,Combustion Turbine,09/01/2009 5:00 PM,MV90 Hourly,0.0

I deifned the timestmap format as MM/DD/YYYY h:mm a, but when I run the Load script, the pivot chart I use to display the dates fails to show me values. This chart has calculated dimension base don the timestamp parameter such as year(timestamp), day(timestamp), month(timestamp)

Is there a better way to Load timestamp data from a csv file?

reading timestamps from a csv

prieper — Sat, 14 Nov 2009 00:21:41 GMT

Have you tried with

TimeStamp(TimeStamp#(Text, 'MM/DD/YYYY h:mm a'))

The inner one tries to interprete the given string as a timestamp as per the specification, the outer one would handle this as date and not as number (if able to read)

HTH
Peter

reading timestamps from a csv

Oleg_Troyansky — Sat, 14 Nov 2009 00:24:06 GMT

Before you get to the chart, check what kind of data are you getting from the load. If QlikView treats your timestampe as a string, you need to cast it into a date using function date# or timestampe# - something like the following:

timestamp(MyStringDate, 'MM/DD/YYYY h:mm a')

good luck!

reading timestamps from a csv

Sat, 14 Nov 2009 00:47:01 GMT

Olef, Itried different suggestions on handling the timestamp string a load time.. Here is an excerpt of what I am trying to do

let path = 'C:\QlikView QVW\DataSources\meterData\';
let outPath = 'C:\QlikView QVW\DataSources\qvd\';

MeterData:
LOAD @1 as meterName,
@2 as unitName,
@3 as planName,
@4 as market,
@5 as fuelType,
timestamp(@6,'MM/DD/YYYY h:mm a') as beginDate,
@7 as editionName,
@8*-1 as Value
FROM
$(path)GEN_METER*.csv
(txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines);

I checked loading this and when creating a listbox for beginDate, QLIKView interprets the beginDate as a string since when i navigate to the list box properties, Number and change to NUmber, Date or Timestamp, it always returns the string value imported.

Regards,

reading timestamps from a csv

Oleg_Troyansky — Sat, 14 Nov 2009 00:48:28 GMT

add '#' after the timestamp function, and use 'tt' instead of 'a' for AM/PM, and you should be fine

reading timestamps from a csv

Sat, 14 Nov 2009 00:48:54 GMT

Oleaf I tried suggestion but still the timestamp is interpreted in qlikview as a string


let path = 'C:\QlikView QVW\DataSources\meterData\';
let outPath = 'C:\QlikView QVW\DataSources\qvd\';
MeterData:
LOAD @1 as meterName,
 @2 as unitName,
 @3 as planName,
 @4 as market,
 @5 as fuelType,
 timestamp(@6,'MM/DD/YYYY h:mm a') as beginDate,
 @7 as editionName,
 @8*-1 as Value
FROM
$(path)GEN_METER*.csv
(txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines);

reading timestamps from a csv

Sat, 14 Nov 2009 00:50:12 GMT

Oleaf I tried suggestion but still the timestamp is interpreted in qlikview as a string


let path = 'C:\QlikView QVW\DataSources\meterData\';
let outPath = 'C:\QlikView QVW\DataSources\qvd\';
MeterData:
LOAD @1 as meterName,
 @2 as unitName,
 @3 as planName,
 @4 as market,
 @5 as fuelType,
 timestamp(@6,'MM/DD/YYYY h:mm a') as beginDate,
 @7 as editionName,
 @8*-1 as Value
FROM
$(path)GEN_METER*.csv
(txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines);

reading timestamps from a csv

Sat, 14 Nov 2009 00:50:58 GMT

Oleaf I tried suggestion but still the timestamp is interpreted in qlikview as a string


let path = 'C:\QlikView QVW\DataSources\meterData\';
let outPath = 'C:\QlikView QVW\DataSources\qvd\';
MeterData:
LOAD @1 as meterName,
 @2 as unitName,
 @3 as planName,
 @4 as market,
 @5 as fuelType,
 timestamp(@6,'MM/DD/YYYY h:mm a') as beginDate,
 @7 as editionName,
 @8*-1 as Value
FROM
$(path)GEN_METER*.csv
(txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines);

reading timestamps from a csv

Oleg_Troyansky — Sat, 14 Nov 2009 00:52:24 GMT

You keep modifying my name, but more importantly you keep using function "timestamp" instead of function "timestamp#" - those are actually 2 different functions, and they are doing 2 different things. You need the pound sign # at the end of your function name.

reading timestamps from a csv

Sat, 14 Nov 2009 00:55:27 GMT

Oleg , I tried your suggestion but still qlikview interprets beginDate as a String and my calculated dimensions fail to display on my pivot i.e. year(beginDate)

Her eis my code:

MeterData:

LOAD @1 as meterName,

@2 as unitName,

@3 as planName,

@4 as market,

@5 as fuelType,

timestamp#(@6,'MM/DD/YYYY h:mm a') as beginDate,

@7 as editionName,

@8*-1 as Value

FROM

$(path)GEN_METER*.csv

(txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines);

reading timestamps from a csv

prieper — Sat, 14 Nov 2009 01:06:49 GMT

Please check Oleg's message: you also should please replace the 'a' with 'tt' in the timestamp-function

Peter

reading timestamps from a csv

Oleg_Troyansky — Sat, 14 Nov 2009 01:09:03 GMT

you missed one more correction - 'tt' instead of 'a'. Try this:

MeterData:

LOAD @1 as meterName,

@2 as unitName,

@3 as planName,

@4 as market,

@5 as fuelType,

timestamp#(@6,'MM/DD/YYYY h:mm tt') as beginDate,

@7 as editionName,

@8*-1 as Value

FROM

$(path)GEN_METER*.csv

(txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines);

reading timestamps from a csv

Sat, 14 Nov 2009 02:03:20 GMT

Thanks! replacing a with tt did the trick

reading timestamps from a csv

Sat, 14 Nov 2009 02:04:43 GMT

replacing a with tt did the trick.. Thanks!