topic Problem to load Date from Excel! in QlikView

Problem to load Date from Excel!

Thu, 11 Mar 2010 20:03:46 GMT

Hi,

The date on excel is a numeric value.

40452 is 2010-10-01 (YYYY-MM-DD)

While loading the 40452 from the Excel file, how can I load it as a date type value?

Best Regards,

Cassiano

Problem to load Date from Excel!

Thu, 11 Mar 2010 20:07:14 GMT

Use "Date(Excel_date_field) AS Date_Field" in your load script.

Problem to load Date from Excel!

Thu, 11 Mar 2010 20:20:10 GMT

Hi,

This function is not working.

Attached is the QV file.

Problem to load Date from Excel!

Thu, 11 Mar 2010 20:21:03 GMT

Here is the excel file that I'm using.

Problem to load Date from Excel!

Thu, 11 Mar 2010 20:42:48 GMT

Here is a link about the excel serial date

http://www.codeproject.com/KB/datetime/exceldmy.aspx

I hope that this can help.

Problem to load Date from Excel!

Thu, 11 Mar 2010 20:44:55 GMT

The combination of purgechar and date have worked.

date (purgechar(ExcelDate,'[]'),'DD/MM/YYYY')

Tks,

Cassiano

Re. :Re: Problem to load Date from Excel!

Tue, 20 Jul 2010 22:15:55 GMT

Hi all

I have the same problem with a crosstab, I can't make a date. Have you a solution:

I have made this script

SourceXLS:
CrossTable(periode, montant, 3)
LOAD catégorie,
[sous catégorie],
détail,
[40179.000000],
[40210.000000],
[40238.000000],
[40269.000000],
[40299.000000],
[40330.000000],
[40360.000000],
[40391.000000],
[40422.000000],
[40452.000000],
[40483.000000],
[40513.000000],
[40544.000000]
FROM
C:\Users\paul\Documents\perso\Comptes.xlsx
(ooxml, embedded labels, table is Comptes, filters(
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null))
));

myDataFile:
NOCONCATENATE
LOAD *
,date#(periode,'MM/DD/YYYY') as myDate
RESIDENT SourceXLS

but it doesm't succedd

Please help me

Re. :Re: Problem to load Date from Excel!

Thu, 22 Jul 2010 15:54:47 GMT

I had the same situation.

I have loaded the crosstable into a tempo table and made the purgechar...

Table1:

NOCONCATENATE

LOAD *,

date (purgechar(tempDate,'[]'),'DD/MM/YYYY') as Date

RESIDENT temp_Cross;

DROP

TABLE

temp_Cross;

DROP

FIELD

tempDate;

Problem to load Date from Excel!

Fri, 23 Jul 2010 13:53:38 GMT

Try this !

Change the formate of cell in excel sheet as Date *3/10/2001 and local as english(us)

date#

date#(tempData,'YY.MM.DD') as Data

you will get like this

is this ok

Naren.

(tempData) as TB,

then use this in script..

Problem to load Date from Excel!

Fri, 23 Jul 2010 14:27:32 GMT

date(num#(tempData,'########'),'DD/MM/YYYY') as date,

the above code will work fine.

Problem to load Date from Excel!

Mon, 20 Feb 2012 14:41:01 GMT

Hello

Same problem, Try this:

DATE(LEFT(FLOOR(NUM(purgechar(tempData,'[.]'))),5)) as date,

Luck!

Problem to load Date from Excel!

Mon, 20 Feb 2012 15:05:14 GMT

Use this sentence on a sript:

Date(Num(Evaluate(tempData) )) as newDate

Re: Re. :Re: Problem to load Date from Excel!

sasiparupudi1 — Sat, 10 Mar 2018 10:52:52 GMT

Rename Column Names Programatically in the Qlikview Script