Solved: Trouble converting text to date field - Qlik Community

Report Inappropriate Content · ‎2011-01-27

Hi,

I am trying to import some excel data. The data is an Excel cross table. The top row represents the date, and the columns the values.

I can't get qlikview to recognise that the imported data is a date field. Ive tried using the date() function, the date#() function, the date(num()) combination, num() on its own... nothing works.

If I try to use a function such as the above in the script, then qlikview doesn't load any data in at all. If I don't use a function then I get data such as 40428, 40429 etc but qlikview won't recognise this as a date field. The field is formatted as a date field in the original excel data.

Thanks

Dan

hector · ‎2011-02-01

Hi, try this, it worked for me


TMP_SCADA:
CrossTable(Day, DayValue, 3)
LOAD F1,
 Average,
 F3,
 [40422],
 [40423],
 [40424],
 ......
 [40563],
 [40564]
FROM
[3286.SCADA%20Sample.xls]
(biff, embedded labels, table is SCADA$);

SCADA:
Load
 *,
 Num(trim(Day)) as Day_Num,
 Date(trim(Day)) as Day_Date
resident TMP_SCADA;

The problem was some hidden spaces

Rgds

View solution in original post

prieper · ‎2011-01-27

You have to reload the crosstable and the apply the formatting as date, script might be somewhat like

tempData: CROSSTABLE (Date#, Sales) LOAD * FROM ...;
Data: LOAD DATE(Date#), Sales RESIDENT tempData;
DROP TABLE tempDate;

edit: corrected "," to ";" in the last line

A preceding LOAD, neither implementing a calculation) does not work with Crosstable.

HTH
Peter

Report Inappropriate Content · ‎2011-01-27

Hello Dan,

I hada similar problem in the past where my date was something like 12/24/2009 00:00:00 and I had to use something like Date#((fieldName) 'MM/DD/YYYY hh:mm:ss') as Date and then it worked for me.

Mama

Report Inappropriate Content · ‎2011-01-27

Hi ,

Whenever i use cross tables i always do a resident load just after, so I have done this. Ive tried using the date() functions in the resident load but it won't work. Here is my script:

** tried to paste code, wouldn't work, see below screenshot **

My god its horrible pasting code into this text editor.

Report Inappropriate Content · ‎2011-01-27

Im sorry i have tried 3 times to edit that post to show the script properly and it won't work.

Report Inappropriate Content · ‎2011-01-27

Report Inappropriate Content · ‎2011-01-27

Image (hopefully...)

prieper · ‎2011-01-31

What does not work in the script? Looks pretty much o.k., although you may also use NOCONCATENATE LOAD instead of the Qualify - Unqualify. Think that you have added, as otherwise the SCADA would be attached to the SCADATemp, as they share the same fieldnames.

In order not to be tied to the column-header in Excel, would prefer a script like:

SCADATemp: CROSSTABLE (Date, Value, 1 ) LOAD * FROM ....;
SCADA: LOAD F1 AS Datapoint, DATE(Date) AS Date, Value RESIDENT SCADATemp;
DROP TABLE SCADATemp;

This script has the advantage, that you will not run into an error, once a column is missing in the Excel.

HTH
Peter

Report Inappropriate Content · ‎2011-02-01

If I use the script with Date(Date) As Date, then I don't get any data loaded at all:

If I use the script without using the date function, then I get this:

But whatever I try I can't get qlikview to recognise or format this as a date.

prieper · ‎2011-02-01

Might be helpful, if you can attach an example to have a closer look.
Just guessing: It might be a bit tricky for the interpreter to differentiate between Date as Field and as Function - so might be worth to rename.
Have you tried to force Date to be displayed as Date (Settings -> Document Properties -> Number)?
Load the Field with the NUM-function in order to force QV to consider as Number?

a bit puzzled ..
Peter