Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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