Solved: Week Nr is not working - Qlik Community

Anonymous · ‎2013-01-07

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

hic · ‎2013-01-08

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

View solution in original post

hic · ‎2013-01-07

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

Anonymous · ‎2013-01-08

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...

Anonymous · ‎2013-01-08

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

hic · ‎2013-01-08

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

Anonymous · ‎2013-01-08

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

hic · ‎2013-01-08

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

HIC

hschultz · ‎2013-01-08

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

Anonymous · ‎2013-01-08

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);

Michiel_QV_Fan · ‎2013-01-08

try:

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