topic Re: Week Nr is not working in QlikView

Week Nr is not working

Anonymous — Mon, 07 Jan 2013 14:59:47 GMT

Hello,

I'm trying to get the WeekNr from a timestamp, but unfortunately it's not working.

I've tried several formulas:

Week( Date (mydate, 'DD.MM.YYYY')) as xy

Week( Date#...

Week( Timestamp# (...

At the beginning of the scrip I have following:

Set DateFormat = 'DD.MM.YYYY';

I've also checked the OS - Date, Time and Numberformats which are 'DD.MM.YYYY'

The interresting thing is that Date(mydate) returns also '-'

Maybe because 'mydate' is sometimes shown as a number and sometimes as 'dd.mm.yyyy hh:mm:ss'

Any ideas??

Cheers

//chesterluck

Re: Week Nr is not working

hic — Mon, 07 Jan 2013 15:09:47 GMT

You need to interpret the dat/timestamp format correctly before you can apply the week() function.

Week(timestamp#(date,'DD.MM.YYYY hh:mm:ss')) should work. If it doesn't, then your date format is different from the format code.

HIC

Re: Week Nr is not working

Anonymous — Tue, 08 Jan 2013 08:01:03 GMT

Thanks the solution is quite helpful.

I wrote before that the tablefield 'mydate' is shown sometimes as number and sometimes as 'DD.MM.YYYY hh:mm:ss'.

For the fields where its shown as 'DD.MM.YYYY hh:mm:ss' everything works fine but for the others the problem unfortunately remains.

I'll try to cleanse it through

Timestamp#(mydate, 'DD.MM.YYYY hh:mm:ss') as mydateNEW

but I think still the problem will remain...

Re: Week Nr is not working

Anonymous — Tue, 08 Jan 2013 08:14:45 GMT

mydateNEW is also not working..

When im exporting the dataset to excel and format it there everything works fine.

40182.865763889

04.01.2010 20:46

04.01.2010 20:46:42

me do not understand

Re: Week Nr is not working

hic — Tue, 08 Jan 2013 08:25:59 GMT

Dates in QlikView are dual, i.e. they have both a numeric value and a textual value. When loaded correctly, the field should have a numeric value like 40182.865 and a textual value like '04.01.2010 20:46'.

With the date#() function (and timestamp#(), time#(), etc.) you interpret a string (e.g. '04.01.2010 20:46') so that you get the correct number.

With the date() function (and timestamp(), time(), etc.) you format a number so that you get the correct string.

Since you have the value 40182.86, it seems like the interpretation is OK but the formatting somehow has been lost. Try nesting the functions like date(date#(...)).

See more on http://community.qlik.com/docs/DOC-3102

HIC

Re: Week Nr is not working

Anonymous — Tue, 08 Jan 2013 09:51:38 GMT

Thx for the hint,

i've tried

timestamp(timestamp#(mydate, 'DD.MM.YYYY hh:mm:ss')) as mydateNEW

but its still not working..

depressing it is

Re: Week Nr is not working

hic — Tue, 08 Jan 2013 10:06:22 GMT

Can you post some data? Because this should work...

HIC

Re: Week Nr is not working

hschultz — Tue, 08 Jan 2013 10:17:30 GMT

Date are a bit funny in QV. I've even had dates showing in QV, but exporting as numbers.

May i suggest trying to manipulate your date with text() and date() as required

hopefully that helps

Re: Week Nr is not working

Anonymous — Tue, 08 Jan 2013 10:29:47 GMT

here the qvd with some sample data.

the script looks like this:

Result1:

LOAD

lMacStaRef,
Auftragsnummer,
Beginn,
Ende,
Schichtdatum,
Timestamp(Timestamp#(Schichtdatum, 'DD.MM.YYYY hh:mm:ss')) as SchichtdatumNeu,

tBegin,
tEnd,

Week(timestamp#(Schichtdatum,'DD.MM.YYYY hh:mm:ss')) as Woche

FROM
..\..\02_Data\Discussion.qvd
(qvd);

Re: Week Nr is not working

Michiel_QV_Fan — Tue, 08 Jan 2013 10:37:07 GMT

try:

week(date(timestamp#(Schichtdatum,'DD.MM.YYYY hh:mm:ss'))) as Woche

Re: Week Nr is not working

Anonymous — Tue, 08 Jan 2013 10:43:34 GMT

No Henric is right the problem is the Schichtdatum/mydate and the

NEST FUNCTIONS

is somehow not working ...

Re: Week Nr is not working

Michiel_QV_Fan — Tue, 08 Jan 2013 10:53:18 GMT

Double checked for any typ errors?

I might be a very small issue

Re: Week Nr is not working

Anonymous — Tue, 08 Jan 2013 11:00:26 GMT

check the discussion:

In Excel the dates / timestamps are shown just fine (after format)..

Ive uploaded a data set.

Really no idea why thats not working..

cheers

Re: Week Nr is not working

swuehl — Tue, 08 Jan 2013 11:11:18 GMT

Your Schichtdatum numbers are not numbers, but read in as text (probably due to the different decimal separator), try:

LOAD

...

Timestamp(alt(num#(Schichtdatum,'#','.',','),Timestamp#(Schichtdatum, 'DD.MM.YYYY hh:mm:ss'))) as SchichtdatumNeu,

...

Re: Week Nr is not working

hic — Tue, 08 Jan 2013 11:42:19 GMT

You have several problems here. The first thing I see when I load your qvd, is that the Schichtdatum is left-aligned, i.e. the numbers are not interpreted as numbers (green). They should be right-aligned. Also, you have a mixture of numbers and dates (red).

Also, when I open the qvd in notepad, I see that the field Schichtdatum has not been correctly interpreted/formatted before it was stored in the qvd.

I would try to create a correct qvd, by using the following formula before the data is stored in the qvd:

Timestamp(Alt(Num#(Schichtdatum,'#','.',','), Timestamp#(Schichtdatum,'DD.MM.YYYY hh:mm:ss')))

HIC

Re: Week Nr is not working

Anonymous — Tue, 08 Jan 2013 12:12:25 GMT

and its working

many thx