topic Transform datefield in QlikView

Transform datefield

Thu, 20 May 2010 10:09:19 GMT

Buildning my first application and have run into problems with an excelfield that i have as source data. The format is yyyy-mm-dd hh.mm.ss and i want to transform it into year ([Entrydate]) as year in the Load statement but dont get no data from it (also the same tranformation with month).

How can i transform it, either in the load or in the designer?

Transform datefield

Thu, 20 May 2010 10:17:41 GMT

Hi,

Can you pl upload your sample data here, so that it will be easy for us to suggest a solution.

-Peterson

Transform datefield

Thu, 20 May 2010 10:24:38 GMT

Source field looks like this:

2010-01-12 10:22:51

2010-01-19 10:24:55

2010-01-15 11:59:26

Load statement looks like this:

LOAD

Company,
[Reported by],
[Call id],
[External id],
Status,
Priority,
[Int. Ref. Id],
Closed,
[Enter Hours],
Left ([Enter Date], 10),
Year ([Enter Date]) as År,
Month ([Enter Date]) as Månad,
[Work Hours]
FROM
[\\xd40201.u-dom1.u-ssi.net\sema20106user$\H15992\work\QV\DATA\Logicauppföljning\E ON Jan 2010.xls]
(biff, embedded labels, table is [FU 1001$])
Where [Company] = 'E.ON ELNÄT SVERIGE AB';

Transform datefield

sparur — Thu, 20 May 2010 10:40:09 GMT

Hello try this:

LOAD ...

Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss') as EnterDate,

year(Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss')) as Year

FROm...

Transform datefield

Thu, 20 May 2010 10:48:06 GMT

Thanks, didn't help though. Mayby I took you too literate?

pasted in error statement that showed while loading:

Okänd sats <=Unknown ?
Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss') as EnterDate,

year(Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss')) as Year

Januari:
LOAD Company,
[Reported by],
[Call id],
[External id],
Status,
Priority,
[Int. Ref. Id],
Closed,
[Enter Hours],
Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss') as EnterDate,
Year (Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss')) as Year,
Month (Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss')) as Month,

[Work Hours]
FROM
[\\xd40201.u-dom1.u-ssi.net\sema20106user$\H15992\work\QV\DATA\Logicauppföljning\E ON Jan 2010.xls]
(biff, embedded labels, table is [FU 1001$])
Where [Company] = 'E.ON ELNÄT SVERIGE AB'

Transform datefield

sparur — Thu, 20 May 2010 11:00:47 GMT

Sorry,

you should use a timestamp#() function instead Date#()

Transform datefield

Thu, 20 May 2010 11:01:20 GMT

Hi,

Try the below script


date#(Left([Enter Date],10),'YYYY-MM-DD') as EnterDate,
Year(date#(Left([Enter Date],10),'YYYY-MM-DD') ) as Year

-Peterson