Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
28/10/2015 00:00 |
29/10/2015 00:00 |
29/10/2015 00:00 |
30/10/2015 00:00 |
30/10/2015 00:00 |
02/11/2015 00:00 |
02/11/2015 00:00 |
02/11/2015 00:00 |
Please assist.
Please note, the field is not in date format.
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
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]
Or you can use this: week(date(date#([Last Drop Plan Date Start], 'DD/MM/YYYY hh:mm')))
if th field is a text you can use Date# as interpretation function and then Week funtion
X:
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
28/10/2015 00:00
29/10/2015 00:00
29/10/2015 00:00
30/10/2015 00:00
30/10/2015 00:00
02/11/2015 00:00
02/11/2015 00:00
02/11/2015 00:00
];
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