Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
add '#' after the timestamp function, and use 'tt' instead of 'a' for AM/PM, and you should be fine
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
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!
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,
add '#' after the timestamp function, and use 'tt' instead of 'a' for AM/PM, and you should be fine
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);
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);
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);
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.