topic Whats up with year in QlikView

Whats up with year

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

I am importing an excel sheet with 2017 text.

I am trying to convert it to a YEAR in QV and it appears as follows.

I've tried

Date#(F1, 'YYYY') appears as 2017.00000

Date(F1, 'YYYY') appears as 1905

Year(F1) appears as 1905

Re: Whats up with year

sunny_talwar — Mon, 16 Apr 2018 13:26:45 GMT

May be try this

Year(Date#(F1, 'YYYY')) as Year

Re: Whats up with year

bobbydave — Mon, 16 Apr 2018 13:30:51 GMT

Appearing as blank now.

Screenshot attached

Re: Whats up with year

sasiparupudi1 — Mon, 16 Apr 2018 13:44:48 GMT

try round(F1)

Re: Whats up with year

bobbydave — Mon, 16 Apr 2018 13:54:51 GMT

same as above. blank

Re: Whats up with year

sasiparupudi1 — Mon, 16 Apr 2018 14:03:12 GMT

Year(Trim(Subfield(F1,'.',1)))

Year(Date#(Trim(Subfield(F1,'.',1)),'YYYY'))

Re: Whats up with year

sunny_talwar — Mon, 16 Apr 2018 14:03:18 GMT

Can you try this

Num(Num#(F1))

Num(F1 * 1)

Re: Whats up with year

niclaz79 — Mon, 16 Apr 2018 14:04:43 GMT

Floor(Date#(F1, 'YYYY'))

Re: Whats up with year

marcus_sommer — Mon, 16 Apr 2018 14:08:13 GMT

It seems that your field contained a string with numbers whereby it isn't a date else it's just a year-value. Therefore try something like this:

num(F1)

num(num#(F1, '####.######', '', '.'))

- Marcus

Re: Whats up with year

MarcoWedel — Mon, 16 Apr 2018 21:13:53 GMT

another one:

MakeDate(F1)

Re: Whats up with year

Anonymous — Tue, 17 Apr 2018 07:09:27 GMT

Try this

Year(Date#(DATE,'YYYY.000000')) AS YEAR

Re: Whats up with year

MarcoWedel — Tue, 17 Apr 2018 19:24:55 GMT

please post a sample of your excel file.