Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

reading timestamps from a csv

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?

1 Solution

Accepted Solutions
Oleg_Troyansky

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

View solution in original post

13 Replies
prieper
Master II
Master II

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!

Not applicable
Author

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

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

View solution in original post

Not applicable
Author

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


Not applicable
Author

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


Not applicable
Author

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.

Not applicable
Author

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