topic Weekstart not working in QlikView

Weekstart not working

Tue, 22 Oct 2013 14:31:16 GMT

Hello all,

I'm hoping someone out there can help me solve what I expect is a simple problem that I can't figure out myself. I need to:

1. Take a time value (e.g. 2013-09-05T13:30:44Z) and turn it into a date format.

2. Save the date in a new field.

3. Take that date and find the Thursday of the previous week.

4. Save the previous Thursday as a new field.

Step 4 is where my code below is not working. I get blank "Created_On_Week" values. Any help would be greatly appreciated!

-Josh

SET DateFormat='MM/DD/YYYY';

Table_one:

LOAD created_date_time,

MakeDate(

Left(created_date_time, 4),

Mid(created_date_time,6,2),

Mid(created_date_time,9,2)) as [created_on_date],

WeekStart(created_on_date, 0, -4) as [Created_on_Week] <--- This doesn't work

FROM [<URL removed for posting>] (XmlSimple, Table is [DATA/data]);

Re: Weekstart not working

Clever_Anjos — Tue, 22 Oct 2013 14:45:11 GMT

Table_one:
LOAD *,
WeekStart(created_on_date, 0, -4) as [Created_on_Week] ;
LOAD created_date_time,
MakeDate(
  Left(created_date_time, 4),
  Mid(created_date_time,6,2),
  Mid(created_date_time,9,2)) as [created_on_date];  <--- This doesn't work
FROM [<URL removed for posting>] (XmlSimple, Table is [DATA/data]);

You´re tryning to use a function that expects a date and passing a string as parameter.

Using a pre-Load we can use the value of created_on_date

Best regards

Re: Weekstart not working

Tue, 22 Oct 2013 14:58:25 GMT

Newbie mistake! Many thanks...I knew it would be a quick fix.

-Josh

Re: Weekstart not working

marcus_sommer — Tue, 22 Oct 2013 15:03:56 GMT

This would also work in a single load then such strings could be directly converted.

Table_one:

LOAD date(date#(keepchar('2013-09-05T13:30:44Z', '0123456789-:'), 'YYYY-MM-DDhh:mm:ss'), 'MM/DD/YYY hh:mm:ss') as created_date_time

date(date#(keepchar('2013-09-05T13:30:44Z', '0123456789-:'), 'YYYY-MM-DDhh:mm:ss'), 'MM/DD/YYY') as created_on_date,

weekstart(date(date#(keepchar('2013-09-05T13:30:44Z', '0123456789-:'), 'YYYY-MM-DDhh:mm:ss'), 'MM/DD/YYY'), 0, -4) as Created_on_Week

FROM [<URL removed for posting>] (XmlSimple, Table is [DATA/data]);

- Marcus