topic Trouble converting text to date field in QlikView

Trouble converting text to date field

Thu, 27 Jan 2011 16:09:26 GMT

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

Trouble converting text to date field

prieper — Thu, 27 Jan 2011 16:14:06 GMT

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

Trouble converting text to date field

Thu, 27 Jan 2011 16:18:19 GMT

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

Trouble converting text to date field

Thu, 27 Jan 2011 16:40:23 GMT

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.

Trouble converting text to date field

Thu, 27 Jan 2011 16:47:08 GMT

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

Trouble converting text to date field

Thu, 27 Jan 2011 16:52:45 GMT

Trouble converting text to date field

Thu, 27 Jan 2011 16:58:51 GMT

Image (hopefully...)

Trouble converting text to date field

prieper — Mon, 31 Jan 2011 11:47:34 GMT

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

Trouble converting text to date field

Tue, 01 Feb 2011 11:32:28 GMT

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.

Trouble converting text to date field

prieper — Tue, 01 Feb 2011 14:58:32 GMT

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

Trouble converting text to date field

Tue, 01 Feb 2011 15:12:28 GMT

Hi,

Ive tried renaming the field from the crosstable and in the resident load. No effect.

Ive tried using the num() function and the date(num()) function. No effect.

I attach a sample of the source excel data.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/3286.SCADA-Sample.xls]

Trouble converting text to date field

hector — Tue, 01 Feb 2011 16:16:14 GMT

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

Trouble converting text to date field

Tue, 01 Feb 2011 16:24:48 GMT

lol so simple! Thanks.