reading timestamps from a csv

[#1]

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?

[prieper]

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

[Oleg_Troyansky]

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!

[#4]

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,

[Oleg_Troyansky · Accepted solution]

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

[#6]

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

[#7]

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

[#8]

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

[Oleg_Troyansky]

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.

[#10]

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