topic Re: Loading Week Year in QlikView

Loading Week Year

Mon, 23 Nov 2015 15:36:04 GMT

Hi;

I have a field called [Last Drop Plan Date Start] which l want to use to populate week year number. How do l manipulate this field to give me week year number for example week 45, week 47 etc.

Last Drop Plan Date Start

28/10/2015 00:00

29/10/2015 00:00

30/10/2015 00:00

02/11/2015 00:00

Please assist.

Re: Loading Week Year

Mon, 23 Nov 2015 15:36:53 GMT

Please note, the field is not in date format.

Re: Loading Week Year

thomaslg_wq — Mon, 23 Nov 2015 17:27:59 GMT

Hi,

First point, is it ok that these dates are not in the same format ?

30/10/2015 00:00 is text and it's october,

02/11/2015 00:00 is date and it's february.

You could use if(isnull(num([Last Drop Plan Date Start])),week(makedate(num(evaluate(left(subfield([Last Drop Plan Date Start],'/',3),4))),num(evaluate(subfield([Last Drop Plan Date Start],'/',2))),num(evaluate(subfield([Last Drop Plan Date Start],'/',1))))),week([Last Drop Plan Date Start])) as Week

But 02/11/2015 is then week 7... because 02 is february in your date format

Re: Loading Week Year

t_chetirbok — Mon, 23 Nov 2015 17:34:41 GMT

Hello!

You can transform your field by string functions, like this:

week(right(left(Date,10),4)&'-'&right(left(Date,5),2)&'-'&left(Date,2))

where Date = [Last Drop Plan Date Start]

Re: Loading Week Year

t_chetirbok — Mon, 23 Nov 2015 18:20:39 GMT

Or you can use this: week(date(date#([Last Drop Plan Date Start], 'DD/MM/YYYY hh:mm')))

Re: Loading Week Year

maxgro — Mon, 23 Nov 2015 18:47:00 GMT

if th field is a text you can use Date# as interpretation function and then Week funtion

load

Week([Last Drop Plan Date Start]) as Week;

load

Date#([Last Drop Plan Date Start], 'DD/MM/YYYY hh:mm') as [Last Drop Plan Date Start]

inline [

Last Drop Plan Date Start

28/10/2015 00:00

29/10/2015 00:00

30/10/2015 00:00

02/11/2015 00:00

];

Re: Loading Week Year

MarcoWedel — Mon, 23 Nov 2015 23:39:24 GMT

Hi,

another interpretation function that is possible:

LOAD Week(Timestamp#([Last Drop Plan Date Start],'DD/MM/YYYY hh:mm')) as [Last Drop Plan Week]
FROM [https://community.qlik.com/thread/192631] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco