Trouble converting text to date field

[#1]

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

[#2]

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]

[hector · Accepted solution]

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

[#4]

lol so simple! Thanks.